Wrong Calculation at Grand Total

[Anonymous]

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 

[Fowmy]

@Anonymous 

Can you share the DAX calculation for "Abs Error New"?

[Anonymous]

Hi @Fowmy ,

Here is the screenshot of the measure.

[Fowmy · Accepted 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)] ) ) )
)

[DoraRS]

What if we have "DC" and "SKU" coming from two different dim tables that are connected to fact sales table?

[Uzi2019]

Hi @Fowmy ,
Your solution actually worked for me. 

Thanks.

[Anonymous]

Hi @Fowmy ,

Thank you so much it is working fine.

[Greg_Deckler]

@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:

https://youtu.be/n4TYhF2ARe8

[Anonymous]

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.