Forum Discussion

jessicarocha's avatar
jessicarocha
Helper IV
4 years ago

Calculate Mean Absolute Error using columns from 4 tables

Hi guys,   I've been trying to calculate the MAE but I am having some problems. I think the solution could have something to do with the summarize function but I am not sure how to use it here.   ...
Need Help
parry2k's avatar
parry2k
Super User
4 years ago

jessicarocha Hey sorry, I saw your file afterward and I realized that columns are in different fact tables.

 

Try this measure:

 

Measure New = 
VAR __table = 
CROSSJOIN ( 
    CALCULATETABLE(VALUES ( 'Dim Subsidiary'[Subsidiary] ), 'Fact Current Data' ),
    CALCULATETABLE( VALUES ( 'Dim Date'[Date] ), 'Fact Current Data' ) 
)
VAR __data = 
ADDCOLUMNS ( 
    __table, 
    "@Historical", CALCULATE ( SUM ( 'Fact Current Data'[History] ) ), 
    "@Forecast", CALCULATE ( SUM ( 'Fact Historical Data'[Forecast] ) ) 
) 
RETURN
SUMX ( __data, ABS ( [@Historical] - [@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.⚡

  • jessicarocha's avatar
    jessicarocha
    Helper IV
    4 years ago

    Hi parry2k . Thank you so much for the help! Sorry for the delayed answer, I was sick.

    So, your measure solved a part of my problem. It calculates the Absolute Percentage Error (APE) per Subsidiary and month correctly. But when I aggregate per subsidiary, something is wrong....
    In addition, I would like to calculate the Mean absolute percentage error(MAPE). Basically just the APE divided by the number of observations (N). In this case, 3. 

    I think the number of observations I was able to get it right with this measure:

    Test N = 

    Calculate(
      DISTINCTCOUNT('Dim Date'[Year Month]),
        
    FILTER(
        'Fact Historical Data','Fact Historical Data'[Forecast done in]= min('Fact Historical Data'[Forecast done in]) 
        ),
    FILTER('Dim Date', 'Dim Date'[Fiscal Year] = "2021/22"),
    FILTER('Fact Current Data', 'Fact Current Data'[History] <> BLANK())
    )

    So it is just missing the aggregation of your metric to be correct by subsidiary. Can you help me with that?

    Thank you so much!

    Kind regards, Jéssica