SOLVED
Home

Excel Cumulative Sum

%3CLINGO-SUB%20id%3D%22lingo-sub-389875%22%20slang%3D%22en-US%22%3EExcel%20Cumulative%20Sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389875%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20cumulate%20incrementing%20values%20down.%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20file%20for%20better%20explanation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-389875%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389956%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Cumulative%20Sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F134141%22%20target%3D%22_blank%22%3E%40Ubaid%20ur%20Rahman%3C%2FA%3E%26nbsp%3B%2C%20I%20tried%20to%20do%20that%20on%20your%20data%20model%2C%20but%20didn't%20catch%20what%20do%20you%20calculate%20in%20it.%20Thus%20emulate%20on%20the%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20288px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100385i0ED5F116F7086B72%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIt%20is%20named%20as%20Range%20in%20data%20model%2C%20the%20measure%20will%20be%3C%2FP%3E%0A%3CPRE%3ECumulativeCountDifd%3A%3DCALCULATE%20(%0A%20%20%20%20SUM%20(%20%5BActual%20value%5D%20)%2FCOUNTROWS(Range)%2C%0A%20%20%20%20FILTER%20(%20ALLEXCEPT%20(%20Range%2C%20Range%5BYear%5D%20)%2C%20Range%5BMonth%5D%20%26lt%3B%3D%20MAX%20(%20Range%5BMonth%5D%20)%20)%0A)%3C%2FPRE%3E%0A%3CP%3EYou%20may%20it%20in%20Power%20Pivot%20and%20result%20is%20in%20second%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389928%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Cumulative%20Sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389928%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20don't%20need%20grand%20total.%20It%20comes%20up%20auto.%20So%20if%20possible%20without%20Grand%20total.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389923%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Cumulative%20Sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389923%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F134141%22%20target%3D%22_blank%22%3E%40Ubaid%20ur%20Rahman%3C%2FA%3E%26nbsp%3Band%20how%20do%20you%20pivot%20first%20column%2C%20what%20gives%202.27%20in%20Grand%20total%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389912%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Cumulative%20Sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Byes!%20as%20shown%20in%20the%20picture%20that%20how%20to%20simply%20apply%20that%20above%20formula%20to%20that%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389909%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Cumulative%20Sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389909%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F134141%22%20target%3D%22_blank%22%3E%40Ubaid%20ur%20Rahman%3C%2FA%3E%26nbsp%3B%2C%20so%2C%20like%20in%20your%20Excel%20file%20example%20but%20within%20PivotTable%2C%20correct%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389884%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Cumulative%20Sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389884%22%20slang%3D%22en-US%22%3EFor%20cumulative%20sum%2C%20the%20formula%20is%3A%3CBR%20%2F%3E%3DSUM(B%244%3AB4)%3CBR%20%2F%3EFor%20cumulative%20average%2C%20as%20what%20it%20seems%20you%20need%2C%20the%20formula%20is%3A%3CBR%20%2F%3E%3DAVERAGE(B%244%3AB4)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389880%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Cumulative%20Sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20see%20the%20picture%20in%20attachment.%20I%20want%20to%20perform%20that%20incrementing%20cummulative%20action%20in%20pivot%20in%20the%20red%20marked%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389878%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Cumulative%20Sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389878%22%20slang%3D%22en-US%22%3EHi%20Baklan%2C%3CBR%20%2F%3Ethanks%20for%20your%20quick%20reply.%20I%20got%20this%20but%20what%20the%20real%20problem%20is%20how%20to%20perfrom%20this%20action%20in%20Pivot%20table.%20I%20have%20a%20month%20coulumn%20form%201%20to%2012.%20Then%20in%20second%20column%20I%20have%20an%20average%20of%20some%20numbers%20repeating%20monthly.%20in%20the%20third%20I%20want%20to%20perform%20the%20above%20action.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389877%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Cumulative%20Sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389877%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F134141%22%20target%3D%22_blank%22%3E%40Ubaid%20ur%20Rahman%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20to%20repeat%20your%20formula%20to%20make%20it%20more%20universal%20when%3C%2FP%3E%0A%3CPRE%3E%3DSUM(B%244%3A%24B4)%2FCOUNT(B%244%3A%24B4)%3C%2FPRE%3E%0A%3CP%3EOr%20you%20mean%20something%20else%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ubaid ur Rahman
Contributor

I need to cumulate incrementing values down.

Please see the attached file for better explanation.

 

Thank you in advance!

@Sergei Baklan 

9 Replies

Hi @Ubaid ur Rahman ,

 

If to repeat your formula to make it more universal when

=SUM(B$4:$B4)/COUNT(B$4:$B4)

Or you mean something else?

Hi Baklan,
thanks for your quick reply. I got this but what the real problem is how to perfrom this action in Pivot table. I have a month coulumn form 1 to 12. Then in second column I have an average of some numbers repeating monthly. in the third I want to perform the above action.

@Sergei Baklan 

 

please see the picture in attachment. I want to perform that incrementing cummulative action in pivot in the red marked area.

 

Thanks in advance

For cumulative sum, the formula is:
=SUM(B$4:B4)
For cumulative average, as what it seems you need, the formula is:
=AVERAGE(B$4:B4)

@Ubaid ur Rahman , so, like in your Excel file example but within PivotTable, correct?

@Sergei Baklan yes! as shown in the picture that how to simply apply that above formula to that column.

@Ubaid ur Rahman and how do you pivot first column, what gives 2.27 in Grand total?

@Sergei Baklan I don't need grand total. It comes up auto. So if possible without Grand total.

Solution

@Ubaid ur Rahman , I tried to do that on your data model, but didn't catch what do you calculate in it. Thus emulate on the table

image.png

It is named as Range in data model, the measure will be

CumulativeCountDifd:=CALCULATE (
    SUM ( [Actual value] )/COUNTROWS(Range),
    FILTER ( ALLEXCEPT ( Range, Range[Year] ), Range[Month] <= MAX ( Range[Month] ) )
)

You may it in Power Pivot and result is in second sheet.