Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Friends,
I have the following data in Power BI.
I have added new column to arrive Error column i.e., Fcst - Actuals.
Error calcualtion should happen at SKU & DC level i.e., for example SKU A has two customers and 1 DC.
One value is -450 and another one is 325 so final erros is -125 and convert this into +ve number ABS(-125).
I have written measure to show this calcualtion and showing perfect.
However, at the total level result is wrong. It is showing the difference of total columns Fcst & Actuals. 4825 - 6520 = 1695.
But the total i need here is the total of the ABS Erro New measure i.e. 3835.
My final result should like this.
Note: When i filter customer it should work accordingly.
Please help me on this here.
@Greg_Deckler @amitchandak @Ashish_Mathur @Jihwan_Kim @ChandeepChhabra
Solved! Go to Solution.
@Anonymous
Please create the following measure:
Error Measure =
SUMX(
SUMMARIZE( Table3, Table3[DC],Table3[SKU]),
ABS( CALCULATE( SUM(Table3[Fest]) - SUM(Table3[Actuals (Ordered PGI)] ) ) )
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Can you share the DAX calculation for "Abs Error New"?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Please create the following measure:
Error Measure =
SUMX(
SUMMARIZE( Table3, Table3[DC],Table3[SKU]),
ABS( CALCULATE( SUM(Table3[Fest]) - SUM(Table3[Actuals (Ordered PGI)] ) ) )
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
What if we have "DC" and "SKU" coming from two different dim tables that are connected to fact sales table?
Hi @Fowmy ,
Your solution actually worked for me.
Thanks.
@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
I also have a video on this topic:
Hi @Greg_Deckler ,
Thank you so much for the reply.
My case seems to be a little different becasue of converting negatives to postives. This is where i am stuck.
For instance, i have written the following measure to get the right total.
This gives me the correct grand total but at row level i am getting wrong answer.
SKU "A" and DC "DC1" ABS Erro should be 125 not 775.
In the actual table it is considering Error value of 325 & -450 as positive numbers and giving the total as 775. But mu requirement is do the calcualtion of 325+(-450) which gives me -125 then convert it into +125.
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |