Aggregation measure - is locking an aggretation level an option?

[Chava1881]

Hello, 

I am new to my company and I was given the task to create a report with Forecast Error and MAPE %, this calculation must be at the category level. Formulas are below for a better understanding of the issue:

FCST ERROR: abs(Orders-FCST)

MAPE %: Sum of error/Orders

Issue: My measure calculates each individual category perfectly; however, as data aggregates, the error gets reduced because it is going up in the hierarchy, for example:

if I want to see the World Wide, the measure uses the total FCST and Total Orders to make both calculations, hence, the error is reduced significantly when comparing vs the category breakdown, as I go up in the hierarchy, the less error there is. that's why I was wondering if a measure can be tied to a column (Category column in this case), so when the number is rolled up to World Wide the error and MAPE are tied to the sum of Forecast, Orders, and forecast error.

The reason I don't use a calculated column for the error is that it locks the error at the sub-region level so when you sum the error, it increases and pushes the MAPE to higher numbers. 

See samples of what I want to achieve below and what I am getting.

What I need to achieve: 

What I get:

Blue: Error calculated by calculated column then MAPE using a measure with this calculated column

Green: MEASURE calculating everything. 

Green Measure below:

MAPE_Agg_Measure = 
VAR TOTAL_FCST = CALCULATE(
                            SUM('Aggregated-Data'[Forecast])
)

VAR TOTAL_ORDERS_RSD = Calculate(
                                sum('Aggregated-Data'[Orders])
)

VAR FCST_Error = abs( TOTAL_ORDERS_RSD - TOTAL_FCST )

Return
FCST_Error/TOTAL_ORDERS_RSD

my question is:

Is there a way I can do this calculation but limit them by category, telling DAX that it needs to aggregate by category and from there it can roll up? - I know this can be done in QlikSense as you can set a column to aggregate calculations- or else am I taking the wrong route here? any thoughts. 

PBIX is attached. 

Click Download to get it 

[parry2k · Accepted solution]

@Chava1881 this measure will give you correct Error sum and then you can further add measure to us this Error measure

Error = 
SUMX ( 
    VALUES ( 'Aggregated-Data'[Category] ), 
    ABS ( 
        CALCULATE ( SUM ( 'Aggregated-Data'[ORDERS] ) )-
        CALCULATE ( SUM ( 'Aggregated-Data'[Forecast] ) )
    )
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Chava1881 is this what you are looking for?

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Chava1881]

Unfortunately not, I need to Total number or WW number to be 18%. The issue I am having is that the right way to calculate it in excel is as shown in the screenshot below:
B2 is summing all FCST

C2 is summing all Orders

D2 is summing the error at the category level.

When using my measure it goes calculating the error by using the total sum of forecast and orders, making the MAPE 1%, and if I create a calculated column, as there are different regions and subregions, the error is higher than calculating it as shown in the excel.

Hope this makes sense.