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.
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?
Solved! Go to Solution.
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:
This should show the expected result. See my test result:
If any further help needed, please feel free to post back.
Regards
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:
This should show the expected result. See my test result:
If any further help needed, please feel free to post back.
Regards
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.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |