I’m attempting to create a report that compares two separate forecast model projections (baseline and iteration1) against actuals and displays the respective weighted MAPE and forecast accuracy for each forecast model. The forecasts and actuals are at the item/location/week level, but the weighted MAPE and forecast accuracy needs to be dynamically calculated across one or more weeks and aggregated based on an item hierarchy of DEPT, CATEGORY, ITEM.
I’m attempting to accomplish this using the matrix visual with a week slicer, however I’m having trouble with the DAX needed to calculate the aggregate values at each item hierarchy level within the correct context. Here are the KPI calculations that we’re using.
Absolute Forecast Error = | (Forecast – Sales) | / Sales
Weighted MAPE = Sales * | (F-A) / A | / SUM Sales
Forecast Accuracy = Max(1 – MAPE, 0)
I’ve attached a sample .pbix along with example data. The “ExpectedResults_CATA” tab shows the expected calculation results when aggregating all CATEGORY A item/locations across all weeks.
How can I modify the Weighted MAPE and Forecast Accuracy DAX calculations to work both at the aggregated and item/location/week granularity?