Forum Discussion
DISTINCT COUNT not visualizing accurately
Hi there,
I am working on a report for our social services division and they want to see demographics of distinct program participants. When I create this using the COUNT (DISTINCT) function using the standard Values options, the matrix shows inaccurate row data but an accurate distinct grand total. I am building this distinct counting the Unique ID for each participant created by our database.
| Demographic | Distinct |
| A | 1321 |
| B | 1071 |
| C | 539 |
| D | 322 |
| E | 239 |
| F | 164 |
| G | 82 |
| H | 65 |
| I | 46 |
| J | 36 |
| K | 5 |
| L | 5 |
| M | 2 |
| Total | 3758 |
If you add up the rows, it equals 3897 not 3758 which confuses the end-user or anyone trying to intelligently analyze this data. The duplicated value is 4879, so it's not the full duplicated amount either and it's not the accurate distinct count - it is somewhere in between. These demographics are not multi-check options (only one Unique ID is assigned to one Demographic variable). Help! I searched all over this forum and none of the suggested measures worked.
Hi Anonymous ,
I think this is by default. As there should be some same IDs for different Demographic in your data. So aiming to get the correct result, we should create a measure based on [Distinct].
Distinctcorrect = sumx(your table, [Distinct])
If the issue still occur, kindly share your pbix to me. Please upload your files to One Drive and share the link here.
Regards,
Frank
3 Replies
Hi Anonymous ,
I think this is by default. As there should be some same IDs for different Demographic in your data. So aiming to get the correct result, we should create a measure based on [Distinct].
Distinctcorrect = sumx(your table, [Distinct])
If the issue still occur, kindly share your pbix to me. Please upload your files to One Drive and share the link here.
Regards,
FrankHi v-frfei-msft ,
I cannot share my pbix as the data involves PII. Since the field containing the demographic data does not allow for multiple values, I am not sure how as you said "be some same IDs for different Demographic in your data".
To be clear, did your measure mean to be formatted like this?
Distinctcorrect = SUMX('My_table_name', [UniqueID])
If so, that did not work. If I misinterpreted what you wrote, feel free to clarify. Thanks.
Hi Anonymous ,
Assuming your data looks like that. And I created relationship between two tables based on Demographic. I create a measure to get the distinctcount of category.
distincount = DISTINCTCOUNT(Table2[category])
After new another measure.
Measure = SUMX(Table2,[distincount])
Please refer to the results as the picture below. If your data is not like this, Could you please create a sample and share the data to me. You can upload the files to One Drive and share the link here.
Regards,
Frank