Aggregated Weighted MAPE and Forecast Accuracy Calculations in Matrix Visual

DAX DAX Calculations
1

Hello,

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?

Thanks!
MK

example_data.xlsx (39.2 KB) FA_Report_lite.pbix (315.9 KB)

2

Hi @_mk7

According to your “MAPE” denominator definition you must ignore the filter context. But It never happens! You could use the CALCULATE function

CALCULATE (
   [Total Adjusted Sales],
    ALL ( LOCATION[Location] ),
    ALL ( 'ITEM'[Item] ),
    ALL ( AD_WEEK_DATES[Ad Week] )
)

I hope this helps you. I appreciate if you could check the question as answered.

Best!
DJ

3

Hi @_mk7, did the response provided by @diego help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

1 Like