Incorrect SUM number?

[longtruong]

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

[Anonymous · Accepted 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/

[Anonymous]

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.

[longtruong]

Hello BarneyL,

Thanks for your response.  Is there any way to apply distinct count on month but count on total?

Regards,

Long

[Tishchenko · Accepted solution]

Try create new key that combine SAPID and MonthID and then do DistinctCount on this new key field

[longtruong]

Hello BarneyL and Tishchenko,

Thank you very much for your information.  It really helps.

Regards,

Long

[Anonymous · Accepted 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/