Incorrect totals in Table View when using DISTINCTCOUNT Function power BI desktop

[amty63]

Hello,

Total is reflecting incorrect in table view when using DISTINCTCOUNT function in DAX.

Test1 = DISTINCTCOUNT('Participants'[ Participant Id])

[v-luwang-msft · Accepted solution]

Hi @amty63 ,

You could create a new measure:

test2 = IF(HASONEVALUE('Participants'[COUNTRY_DESCR]),[Test1], SUMX(ALL('Participants'),'Participants'[Test1]))

final you will see the below:

Wish it is helpful for you!

Best Regards

Lucien

[jdbuchanan71]

@amty63 

The total row in a visual showing a DISTINCTCOUNT does not add up the rows above.  I caluclates the DISTINCTCOUNT without any filter applied to the country_descr.  You must also have some other filters / slicers that are feeding into that visual.  
If you want the total to add up by country you can do something like this.

Participants by country =
SUMX (
    VALUES ( TableName[COUNTRY_DESCR] ),
    CALCULATE ( DISTINCTCOUNT ( 'Participants'[ Participant Id] ) )
)

You will need to change the 'TableName' to match whatever table your [COUNTRY_DESCR] is in.