Forecast Accuracy Check Weighted MAPE Calculation

DAX DAX Calculations
1

I’m wanting to ascertain a Weighted MAPE Forecast Inaccuracy measure as a Positive %(Forecast - Sales) / Sales. Ideally the measure would calculate at the lowest level in each Dimension (i.e by Retail Banner by Week by Product EAN Code) and then aggregate up to a Grand Total figure for a Dashboard Report.

I need to have confidence the results generated are correct, but I’m currently struggling to rationalise the results produced for the Grand Total of ‘MAPE Weighting’ & the Grand Total of ‘Average MAPE % (Wtd)’.

Can anyone help correct my formulae?

Here’s the in depth details below…

‘Sales’ Table contains:

Sales[BI_Ex_Factory_Cases]
    Sales[LE Locked M-2]

‘Base Measures’ contains:

Total BI Ex Factory Cases = sumx(sales, Sales[BI_Ex_Factory_Cases])
Total BI LE Cases (Locked2) = sumx(Sales, Sales[LE Locked M-2])

‘Variance Measures’ contains:

Average MAPE % (Num) =
VAR SUMX_ExFACT = sumx(sales,Sales[BI_Ex_Factory_Cases])
VAR SUMX_LE_Locked = sumx(sales,Sales[LE Locked M-2])
VAR ActualVariance = SUMX_ExFACT - SUMX_LE_Locked
VAR ActualVarianceINT = IF(ActualVariance <0,-1*ActualVariance,ActualVariance)
VAR MAPE = divide(ActualVarianceINT,[Total BI Ex Factory Cases],0)
RETURN
IF(HASONEVALUE(Dates[Month & Year]),
  MAPE, 
   SUMX(values(Dates[Month & Year]),MAPE))

‘Variance Measures’ contains:

    MAPE Weighting = 
    VAR MAPEWtd = SUMX(Sales,Sales[BI_Ex_Factory_Cases]) * [Average MAPE % (Num)]
    RETURN
    IF( HASONEVALUE(    Dates[Month & Year] ),
       MAPEWtd,
            SUMX(   VALUES( Dates[Month & Year]),    MAPEWtd) )

‘Variance Measures’ contains:

Average MAPE % (Wtd) = divide([MAPE Weighting],[Total BI Ex Factory Cases],0)

2

Can you load some sample data with that formula in action? Hard to read in its current state, but grand totals that don’t seem to make sense is more of a context issue than anything.

Enterprise%20DNA%20Expert%20-%20Small

3

Hi, Thanks for your response. Since posting my request, I have changed the model input data and my calculations appear (on the face of it) to be working now.

The Key component was in the measure below ensuring the close bracket was at the end of the line, rather than before the multiplication sign:

    MAPE Weighting = 
    VAR MAPEWtd = SUMX(Sales,Sales[BI_Ex_Factory_Cases] * [Average MAPE % (Num)])

    RETURN
    IF( HASONEVALUE(    Dates[Month & Year] ),
       MAPEWtd,
            SUMX(   VALUES( Dates[Month & Year]),    MAPEWtd) )

Loving Power BI.

Thx
Ross