Page 1 of 1

DISTINCTCOUNT Grand Total incorrect

Posted: Mon Feb 22, 2021 5:47 am
by mlmittelman
Howdy:
This is driving me slowly insane . . . have tried various CALCULATE / SUMX / SUMMARIZE -based measures and, at last, got the SUBTOTALs correct, but the GRAND TOTALs are still not summing the DistinctCount values but (I'm assuming) calculating the DistinctCount values for the entire date range (13M).
Pivot table looks like this:
DISTINCTCOUNT Grand Total example.xlsx
(14.39 KiB) Downloaded 17 times

(please see attached EXCEL)
I am displaying the unique number of names by location that generated hours > 0 for each CODE type. In the attached EXCEL, you can see the subtotals are correct but both Grand Totals are incorrect. Here are my measures:
TABLE Name = DATA
Base Measure:
[DATA_Distinct Count] = CALCULATE ( DISTINCTCOUNT ( DATA[NAME] ) , DATA[HOURs] > 0 )
Measure displayed in attached PIVOT:
[DATA_distinct count_TOTALs] =
= IF ( HASONEVALUE ( DATA[LOCATION] ) ,
[DATA_Distinct Count] ,
SUMX (
VALUES ( DATA[LOCATION] ) ,
[DATA_Distinct Count]  ) )

Any assistance would be greatly appreciated!
Thanks.

Re: DISTINCTCOUNT Grand Total incorrect

Posted: Tue Feb 23, 2021 3:59 pm
by MattAllington
I’m on an iPad, so I can’t see your sample datamodel (just the pivot). At least conceptually, here is the problem with totalling distinctcount.

Imagine you have sales by month by customer A,B,C

Jan
A=2
B=1
A=1

Feb
A=4
C=3

How many distinct customers have purchased?
Jan =2, Feb =2, right? But what do you expect for the total? One answer is 4 and another is 3. Both are correct, depending on the logic you want to use. I call this “totals don’t add up”, and I cover it at the bottom of this article (you should read the entire thing) https://exceleratorbi.com.au/use-sum-vs-sumx/

In your case, you need to replicate the existence of the month columns in the total. Something like this
Sumx(values(table[month ID]),calculate( CALCULATE ( DISTINCTCOUNT ( DATA[NAME] ) , DATA[HOURs] > 0 ))

Re: DISTINCTCOUNT Grand Total incorrect

Posted: Thu Feb 25, 2021 12:24 pm
by mlmittelman
Hi Matt:
Thanks very much for your reply . . . have been playing around with your formula suggestion:
Sumx(values(table[month ID]),calculate( CALCULATE ( DISTINCTCOUNT ( DATA[NAME] ) , DATA[HOURs] > 0 ))

But it seems to be overriding the pivot filter on the CODE values . . . will keep at it; thanks for pointing me in the right direction!

Mike

DATA SAMPLE_distinct count PICTURE.png
DATA SAMPLE_distinct count PICTURE.png (21.29 KiB) Viewed 281 times