SOLVED
Home

Running total in Pivot table (Monthwise incrementation by distinct count)

%3CLINGO-SUB%20id%3D%22lingo-sub-389977%22%20slang%3D%22en-US%22%3ERunning%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389977%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Experts%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20regarding%20running%20total%20in%20pivot.%3C%2FP%3E%3CP%3EPlease%20find%20the%20attachement%20and%20it%20is%20better%20explained%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20in%20advance%20for%20your%20quick%20response.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-389977%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390786%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390786%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%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390766%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390766%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%3BThank%20you%20so%20so%20much%20for%20your%20time%20and%20support.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390742%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390742%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%20that's%20in%20Sheet3%20attached%20based%20on%20your%20sample%20data%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390691%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390691%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%20okay%2C%20for%20the%20yellow%20variant%20you%20shall%20add%20one%20more%20column%20into%20your%20data%20model%2C%20let%20say%20DateKey%2C%20as%3C%2FP%3E%0A%3CPRE%3E%3D%5BYear%5D*100%2B%5BMonth%5D%3C%2FPRE%3E%0A%3CP%3Eand%20modify%20previous%20formula%20as%3C%2FP%3E%0A%3CPRE%3EDistinctCountSuppliers%3A%3DCALCULATE%20(%0A%20%20%20%20DISTINCTCOUNT(%5BSupplier%20No.%5D)%2C%0A%20%20%20%20FILTER%20(%20ALL%20(%20Table1%20)%2C%20Table1%5BDateKey%5D%20%26lt%3B%3D%20MAX%20(Table1%5BDateKey%5D%20)%20)%0A)%3C%2FPRE%3E%0A%3CP%3EDidn't%20test%20it%2C%20will%20illustrate%20on%20the%20sample%20some%20later%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390674%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390674%22%20slang%3D%22en-US%22%3EYes%20that%20will%20be%20great!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390664%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390664%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%20and%20if%20we%20go%20from%20one%20year%20to%20another%2C%20we%20take%20yellow%20variant%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20426px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100507i9F83D1CD16FE14F6%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%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390608%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390608%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%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%3BYes%20I%20want%20running%20distinct%20count%20over%20period.%20as%20you%20mentioned%20in%20exp.%20like%20in%20March%202019%20we%20should%20have%20a%20sum%20of%20distinct%20count%20from%20Jan%20and%20Feb%20and%20so%20on%20at%20the%20end.%3C%2FP%3E%3CP%3E(At%20the%20end%20grand%20total%20should%20also%20sum%20distinct%20count%20for%20the%20whole%20year%20if%20possible)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390598%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390598%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'll%20check%20distinct%20count%20calculations%20by%20another%20way.%20But%20let%20agree%20first%20-%20you'd%20like%20running%20distinct%20count%20over%20periods%20(e.g.%20in%20March%202019%20we%20have%20total%20distinct%20count%20from%20Jan%202018%20to%20Feb%202019%20includes)%2C%20or%20we%20start%20running%20total%20for%20each%20new%20year%20from%20scratch%3B%20or%20we%20sum%20distinct%20counts%20for%20each%20month%20ignoring%20what%20some%20could%20be%20repeated%20in%20full%20period.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390590%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390590%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%20removed%20formulas%20and%20attachment%2C%20hope%20it%20is%20enough%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390573%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390573%22%20slang%3D%22en-US%22%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%20Can%20you%20please%20delete%20this%20post%20as%20it%20contains%20some%20confidential%20information%20and%20I%20dont%20want%20to%20be%20use%20in%20public.%20Thank%20you%20for%20you%20understanding.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390572%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390572%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%20I%20mean%20wrong%20calculations.%20What%20I%20see%20is%20it%20is%20not%20counting%20the%20distinct%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390158%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390158%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%20perhaps%20I%20misunderstood%20you.%20%22Not%20working%22%20means%20wrong%20formula%20syntax%2C%20or%20wrong%20result%20calculated%20by%20that%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390144%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390144%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%20bit%20hard%20to%20recognize%20on%20screenshort%20was%20is%20wrong%2C%20I%20added%20these%20metrics%20for%20your%20model%2C%20please%20see%20third%20sheet%20attached.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390095%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390095%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%3BThank%20you%20for%20your%20quick%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20that%20is%20correct%20but%20now%20the%20problem%20is%20when%20I%20apply%20it%20to%20my%20original%20file%20it%20is%20somehow%20not%20working.%3C%2FP%3E%3CP%3EFor%20confidential%20reason%20I%20did%20extract%20some%20elements%20which%20I%20uploaded.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESee%20in%20the%20picture.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390004%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20total%20in%20Pivot%20table%20(Monthwise%20incrementation%20by%20distinct%20count)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390004%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%20that's%20not%20the%20same%20as%20in%20your%20table.%20If%20you%20have%20distinct%20count%20for%20the%20one%20month%20and%20another%20one%2C%20total%20distinct%20count%20won't%20be%20a%20sum%20since%20some%20suppliers%20are%20repeated%20in%20full%20period.%20Thus%20total%20for%20two%20months%20shall%20be%20less%20than%20sum%20of%20distinct%20count%20for%20each%20of%20two%20months.%20With%20that%20logic%20that%20is%20like%3C%2FP%3E%0A%3CPRE%3EDistinctCountSuppliersTotal%3A%3DCALCULATE%20(%0A%20%20%20%20DISTINCTCOUNT(%5BSupplier%20No.%5D)%2C%0A%20%20%20%20FILTER%20(%20ALL%20(%20Table1%20)%2C%20Table1%5BPurchase%20date%5D%20%26lt%3B%3D%20MAX%20(Table1%5BPurchase%20date%5D%20)%20)%0A)%3C%2FPRE%3E%0A%3CP%3EIf%20distinct%20count%20only%20for%20the%20year%3C%2FP%3E%0A%3CPRE%3EDistinctCountSuppliers%3A%3DCALCULATE%20(%0A%20%20%20%20DISTINCTCOUNT(%5BSupplier%20No.%5D)%2C%0A%20%20%20%20FILTER%20(%20ALLEXCEPT%20(%20Table1%2CTable1%5BYear%5D%20)%2C%20Table1%5BMonth%5D%20%26lt%3B%3D%20MAX%20(Table1%5BMonth%5D%20)%20)%0A)%3C%2FPRE%3E%0A%3CP%3EI%20added%20both%2C%20please%20check%20in%20the%20file.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20you%20assume%20another%20logic%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Dear Experts,

 

I need help regarding running total in pivot.

Please find the attachement and it is better explained there.

 

thank you in advance for your quick response.

15 Replies
Highlighted
Solution

@Ubaid ur Rahman , that's not the same as in your table. If you have distinct count for the one month and another one, total distinct count won't be a sum since some suppliers are repeated in full period. Thus total for two months shall be less than sum of distinct count for each of two months. With that logic that is like

DistinctCountSuppliersTotal:=CALCULATE (
    DISTINCTCOUNT([Supplier No.]),
    FILTER ( ALL ( Table1 ), Table1[Purchase date] <= MAX (Table1[Purchase date] ) )
)

If distinct count only for the year

DistinctCountSuppliers:=CALCULATE (
    DISTINCTCOUNT([Supplier No.]),
    FILTER ( ALLEXCEPT ( Table1,Table1[Year] ), Table1[Month] <= MAX (Table1[Month] ) )
)

I added both, please check in the file.

 

Or you assume another logic?

Highlighted

@Sergei Baklan Thank you for your quick response.

 

Yes that is correct but now the problem is when I apply it to my original file it is somehow not working.

For confidential reason I did extract some elements which I uploaded.


See in the picture.

Highlighted

@Ubaid ur Rahman , bit hard to recognize on screenshort was is wrong, I added these metrics for your model, please see third sheet attached.

 

Highlighted

@Ubaid ur Rahman , perhaps I misunderstood you. "Not working" means wrong formula syntax, or wrong result calculated by that formula?

Highlighted

@Sergei Baklan  I mean wrong calculations. What I see is it is not counting the distinct numbers.

Highlighted
@Sergei Baklan Can you please delete this post as it contains some confidential information and I dont want to be use in public. Thank you for you understanding.
Highlighted

@Ubaid ur Rahman , I removed formulas and attachment, hope it is enough

Highlighted

@Ubaid ur Rahman , I'll check distinct count calculations by another way. But let agree first - you'd like running distinct count over periods (e.g. in March 2019 we have total distinct count from Jan 2018 to Feb 2019 includes), or we start running total for each new year from scratch; or we sum distinct counts for each month ignoring what some could be repeated in full period.

Highlighted

 

@Sergei Baklan Yes I want running distinct count over period. as you mentioned in exp. like in March 2019 we should have a sum of distinct count from Jan and Feb and so on at the end.

(At the end grand total should also sum distinct count for the whole year if possible)

 

Highlighted

@Ubaid ur Rahman , and if we go from one year to another, we take yellow variant?

image.png

 

Highlighted
Highlighted

@Ubaid ur Rahman , okay, for the yellow variant you shall add one more column into your data model, let say DateKey, as

=[Year]*100+[Month]

and modify previous formula as

DistinctCountSuppliers:=CALCULATE (
    DISTINCTCOUNT([Supplier No.]),
    FILTER ( ALL ( Table1 ), Table1[DateKey] <= MAX (Table1[DateKey] ) )
)

Didn't test it, will illustrate on the sample some later

Highlighted

@Ubaid ur Rahman , that's in Sheet3 attached based on your sample data

Highlighted

@Sergei Baklan Thank you so so much for your time and support.

Highlighted
Related Conversations
Counting Days
Tim Hunter in SQL Server on
2 Replies
How to count multiple values in a cell
Ugarte335 in Excel on
7 Replies
Count until
MBelshaw in Excel on
1 Replies
Pivot table
gabriellerocha in Excel on
5 Replies
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies