Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Hello,

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

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

 

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi Anonymous ,

    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

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    Anonymous 

    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.

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    Hi Anonymous ,

    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