Forum Discussion

Richard_S's avatar
Richard_S
Advocate I
10 years ago
Solved

Problem with totals in visuals

I am having an issue with totals that Power BI is calcuating, in the below table I have 2 columns by month, one is "Opportunity ID" using Power BI's 'Distinct Count' Summarization View. the other is ...
Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    10 years ago

    Hi Richard_S,

     

    If you just choose the count(distinct) in Power BI desktop, then the total would show the distinct count of the whole column. This is the same when using the Distinctcount measure.

    To have the total show the expected total here, we need perform the following steps:

    • First create a measure to distinct count the column, which you have already done([Opportunity Count]  := DISTINCTCOUNT([Opportunity ID]));
    • Create another measure with the following formula(put the measure here):
    • [OC] := SUMX(DISTINCT(Table[Close Month]), [Opportunity Count])

    This should show the expected result. See my test result:

    If any further help needed, please feel free to post back.

    Regards

     

v-micsh-msft's avatar
v-micsh-msft
Microsoft Employee
10 years ago

Hi Richard_S,

 

If you just choose the count(distinct) in Power BI desktop, then the total would show the distinct count of the whole column. This is the same when using the Distinctcount measure.

To have the total show the expected total here, we need perform the following steps:

  • First create a measure to distinct count the column, which you have already done([Opportunity Count]  := DISTINCTCOUNT([Opportunity ID]));
  • Create another measure with the following formula(put the measure here):
  • [OC] := SUMX(DISTINCT(Table[Close Month]), [Opportunity Count])

This should show the expected result. See my test result:

If any further help needed, please feel free to post back.

Regards

 

Richard_S's avatar
Richard_S
Advocate I
10 years ago
Thanks for the reply Michael, I actually figured it out shortly after posting here.

Thanks again!