DISTINCT COUNT not visualizing accurately

[Anonymous]

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.

[v-frfei-msft]

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

[v-frfei-msft · Accepted solution]

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

[Anonymous]

Hi @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.