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?
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 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.Ubaid ur Rahman , perhaps I misunderstood you. "Not working" means wrong formula syntax, or wrong result calculated by that formula?
SergeiBaklan I mean wrong calculations. What I see is it is not counting the distinct numbers.
Ubaid ur Rahman , bit hard to recognize on screenshort was is wrong, I added these metrics for your model, please see third sheet attached.
- SergeiBaklan 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.
