Forum Discussion
Running total in Pivot table (Monthwise incrementation by distinct count)
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?
SergeiBaklan 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)
Ubaid ur Rahman , you are welcome
SergeiBaklan Thank you so so much for your time and support.
Ubaid ur Rahman , that's in Sheet3 attached based on your sample data
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
- Yes that will be great!