Forum Discussion

SachinC's avatar
SachinC
Helper V
8 years ago
Solved

Count (Distinct) Totals not working

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.   ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

SachinC's avatar
SachinC
Helper V
8 years ago

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's avatar
prateekraina
Memorable Member
8 years ago

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