Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I want to calculate a difference per ROW rather than the TOTAL by subtracting values from 2 seperate tables.
I thought using SUMX would help me rather than SUM.
Absolute Error = ABS(SUMX(Actuals;Actuals[Actuals]-SUMX('L2 forecasts';'L2 forecasts'[Forecast Qty]))).
However, the number I get is very high. I have tried using CALCULATE and VALUE functions but I'm not getting there. If I change from subtracting to adding up, the number barely changes.
Anyone any idea?
Solved! Go to Solution.
I found the solution.. the formula works as the relationship is established. However I had to create a new column for the calculation, not a new measure! Thanks for your advice 🙂
Hi @Reits,
Do you mean the 2 seperate tables are not related with each other? If so, with above formula, you will get the total values across entire table with SUMX rather than per row. That is why the number you get is very high.
As Floriankx suggested, please generate relationship between two tables.
Regards,
Yuliana Gu
I found the solution.. the formula works as the relationship is established. However I had to create a new column for the calculation, not a new measure! Thanks for your advice 🙂
Hello,
you need to relate the tables
and use something like Absolute Error:= ABS(SUMX(Actuals;Actuals[Actuals]-Related('L2 forecasts'[Forecast Qty]))).