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.⚡