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.
Hello Everyone,
I have first tried PowerBI with a very simple query and got incorrect SUM number.
The correct total must be 373 instead of 318 in the above image. I am not sure if Count (Distinct) of SAP ID causes this problem. Can anyone please explain?
Thanks,
Long
Solved! Go to Solution.
There will be a DAX formula that does it but I'm not exactly sure how (I'm new to DAX but not distinct count issues).
At a guess investigating SUMX might be a good start, it should be possible to get that to sum the distinct count for each month.
Here's a link to what looks like a similar technique:
http://www.powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/
Try create new key that combine SAPID and MonthID and then do DistinctCount on this new key field
If you're using a distinct count then the total won't be a sum of each month it will be another distinct count for all months. IDs that appear in more than one month will be counted only once for the total.
e.g.
The values
Month SAP ID
April 1
April 2
May 1
May 3
Would give 2 for April, 2 for May and 3 for the total because while SAP ID 1 appears in both months but is only counted once for the total.
Hello BarneyL,
Thanks for your response. Is there any way to apply distinct count on month but count on total?
Regards,
Long
Try create new key that combine SAPID and MonthID and then do DistinctCount on this new key field
Hello BarneyL and Tishchenko,
Thank you very much for your information. It really helps.
Regards,
Long
There will be a DAX formula that does it but I'm not exactly sure how (I'm new to DAX but not distinct count issues).
At a guess investigating SUMX might be a good start, it should be possible to get that to sum the distinct count for each month.
Here's a link to what looks like a similar technique:
http://www.powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/
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.