### DISTINCTCOUNT Grand Total incorrect

Posted:

**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:

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

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:

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