Forum Discussion
Wrong Calculation at Grand Total
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
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)] ) ) ) )
8 Replies
- Greg_Deckler
Community Champion
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/547907I 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.
- Fowmy
Super User
Anonymous
Can you share the DAX calculation for "Abs Error New"?
