DISTINCTCOUNT Grand Total incorrect

Anything related to PowerPivot and DAX Formuale
mlmittelman
Posts: 10
Joined: Mon Oct 21, 2019 3:38 am

DISTINCTCOUNT Grand Total incorrect

Postby mlmittelman » Mon Feb 22, 2021 5:47 am

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 16 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.

MattAllington
Posts: 1157
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: DISTINCTCOUNT Grand Total incorrect

Postby MattAllington » Tue Feb 23, 2021 3:59 pm

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 ))
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

mlmittelman
Posts: 10
Joined: Mon Oct 21, 2019 3:38 am

Re: DISTINCTCOUNT Grand Total incorrect

Postby mlmittelman » Thu Feb 25, 2021 12:24 pm

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 278 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 5 guests