Forum Discussion
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 a measure:
Opportunity Count = DISTINCTCOUNT([Opportunity ID])
These are just values from one one table and the only filter applied is for the Close Month (Jan - July) - When i remove the filter the problem remains.
As you can see from the below if Power Bi is performing a sum of the data I would expect the total to be 2681, however the PowerBI result is 1829.
Is there something different with the calculation method in power BI that I'm missing/unaware of?
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
3 Replies
I'm thinking this problem might be that the total row is showing a distinct count of all the IDs, rather than summing the distinct counts of ID for each month. SUMX should be able to fix this by summing the distinct count over months. Something like:
Opportunity Count = SUMX(DISTINCT(Date[Close Month]),DISTINCTCOUNT([Opportunity ID]))
You may need to adjust the parameters to get exactly what you want. See http://www.powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/ for more info on SUMX.
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
- Thanks for the reply Michael, I actually figured it out shortly after posting here.
Thanks again!
