Forum Discussion
Incorrect SUM number?
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
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
5 Replies
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
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/
