Forum Discussion
Incorrect SUM number?
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
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
Hello BarneyL and Tishchenko,
Thank you very much for your information. It really helps.
Regards,
Long
