Count (Distinct) Totals not working

[SachinC]

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.

[v-juanli-msft · Accepted 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

[prateekraina]

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

[SachinC]

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?

[prateekraina]

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