Power BI - Distinct Count not working

[PRFInstitute]

Hi, I've a Pie chart on my dashboard in Power BI. The Pie chart is based on the column that inlcudes duplicates. I have a card on my dashboard that shows me the number of distinct values in that column & also the total number values (no. of rows in excel). When I select "Count" the pie chart works well but when I select "Distinct Count", I expect the Pie chart to change values on only have the unique distinct in it. However, when totalling the numbers of the slices.

[Anonymous]

Hi @PRFInstitute ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

DistinctCountMeasure = CALCULATE(DISTINCTCOUNT(Table[Column]), ALLEXCEPT(Table, Table[Category]))

Replace with the name of your table, with the column you want to count distinct values, and with the column you want to group by. After creating the measure, use it in your Pie chart instead of the original column.

DistinctCountMeasure = CALCULATE(DISTINCTCOUNT('Table'[u-id]), ALLEXCEPT('Table', 'Table'[age]))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[PRFInstitute]

Thank you so much for your response, however this didn't solve my issue. Since I don't have the permission to upload the pbix here, I've uploaded the pbix file to WeTransfer and here is the link for it. This pbix includes a pie chart for Age Range. I'm facing the same issue. It calculates the distinct IDs properly on the card but the same doesn't translate to Power BI.

https://we.tl/t-E7lM0kSyJP

It can be becuase of the other columns I have but I'm not sure about it.

TIA.

[Anonymous · Accepted solution]

Hi @PRFInstitute ,

When you place in the card visual object, the distinguish count counts the number of all non-repeating values in the u-id column only (i.e. un1-un6，un8-9, 8 in total), and when you add a classification of age to the pie chart, the distinguish count counts the number of all non-repeating values in the u-id column according to different ages. But different ages may have the same u-id, so the sum of the numbers shown in the pie chart does not match the card, due to the design of the power bi desktop.

When the age is [45-54], the unduplicated value of u-id is 1.

If you want to display the calculated sum of the unduplicated values of u-ids of different ages (in the example 3+6+1=10) on the card visual object, you can create a measure to be placed on the card visual object.

count of u-id by age range = sumx(VALUES('Unified 2 16 23 v 16b'[D1. Age Range]),'Unified 2 16 23 v 16b'[DistinctCountMeasure])

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.