Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I have a couple of Power BI reports where my Count Distinct Totals aren't working.
See screenshots below. If I don't apply Distinct, the totals are fine. Please help?
Thank you.
Solved! Go to Solution.
Hi
Look at my test
This is my dataset
Then add these columns to a table visual
The second column is "count" of "refer", the third is "distinct count" of "refer", to get what you want:
each line should be distinct count, but the total should be the total of count, just like the last column.
it is a measure
distinctount = IF(HASONEVALUE(Sheet1[name]),DISTINCTCOUNT(Sheet1[refer]),COUNT(Sheet1[refer]))
Best Regards
Maggie
Hi
Look at my test
This is my dataset
Then add these columns to a table visual
The second column is "count" of "refer", the third is "distinct count" of "refer", to get what you want:
each line should be distinct count, but the total should be the total of count, just like the last column.
it is a measure
distinctount = IF(HASONEVALUE(Sheet1[name]),DISTINCTCOUNT(Sheet1[refer]),COUNT(Sheet1[refer]))
Best Regards
Maggie
Hi @SachinC,
Can you please tell what is the expected value as per your logic in Total?
Have you defined any measure for this? If yes, please share the expression.
Giving an example would be helpful.
For Example, Give some sample data and tell what do you expect out of it and what you are currently getting.
Prateek Raina
Hi,
I would have expected a Total based on the values I can see on a line-by-line basis.
I am bringing into the Table view, Name (text) which is the 2nd column in the screenshot and Count of Reference (which is also Text) in the 3rd column in screenshot. Is this why it's not working - I have text fields?
Hi @SachinC,
The results which you are getting is correct. I think you are confused between count and distinct count.
Take a look at below picture to understand what is happening. I have used some dummy data:
I hope you get why the totals are like that in your case.
Here is the pbix of above in case you want to dig in more.
Prateek Raina
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |