Sum Of Measure

[Anonymous]

Hello Everyone!

I am trying to calcule KPI wich is called WMAPE, my data is like the picture bellow, 

I have the following formulas: 

1. Percent Error =
ABS (
    DIVIDE (
        SUM ( 'Table1'[Forecast_final] )
            - SUM ( 'Table1'[Sales] );
        SUM ( 'Table1'[Sales] )
    )
)

2. Actual =  SUM ( 'Table1'[Sales] )

3. Percent Error * Actual = [Percent Error]*[Actual]

Then i need to divide the sum of the measure [Percent Error * Actual]/[Actual], but my problem is that the value in the total is not the sum of the measure, but is the calculated value of all the data, I need this measure beacuse i need to filter the data acording to the SKU and the Warehouse. 

I was trying with this formula but it did not worked:

Suma Correcta =

SUMX (

SUMMARIZE (

'Table1';

'Table1'[PeriodDate];

'Table1'[warehouse];

'Table1'[sku];

'Table1'[Sales];

'Table1'[Forecast_final];

"temp"; (ABS(DIVIDE(SUM('Table1'[Forecast_final])-SUM('Table1'[Sales]);SUM('Table1'[Sales]))))*SUM('Table1'[Sales])

);

IF ( ISERROR ( [temp] ); 0; [temp] ))

Please i need some help!

Kind regards 

CY

[parry2k · Accepted solution]

@Anonymous change your measure like this and test

change your measure like this and test
 
Percent Error * Actual = SUMX( SUMMARIZE( Data, Data[SKU], Data[Warehouse]), [Percent Error]*[Actual] )

[Anonymous · Accepted solution]

@Anonymous 

If you want to calculate the sum of the measure just using SUMX.
Percent Error * Actual = SUMX(Table, [Percent Error]*[Actual])

You should always use X function(sumx, averagex,etc) when calculating with measures, the value of each row then can be saved and calculated.

Best,
Paul

[parry2k]

@Anonymous not sure where you want the final calculation to be, at the total? Based on the screenshot of dataset your provided, what you want output to be?

[Anonymous]

@parry2k  thaks for replying, the thing is the following: 

my data is divided into date, sku and warehouse, my two measures for obtaing the Abs Error and the Abs Error * Sales are working fine, i need that the total of the column be 64315, and my result is giving me this:

I want to calculate the division of the sum (abs error * sales)/sum(sales), this is how looks like in excel 

[parry2k · Accepted solution]

@Anonymous change your measure like this and test

change your measure like this and test
 
Percent Error * Actual = SUMX( SUMMARIZE( Data, Data[SKU], Data[Warehouse]), [Percent Error]*[Actual] )