Sumx calculation per row between 2 tables

[Reits]

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?

[v-yulgu-msft]

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

[Reits · Accepted 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 🙂

[Floriankx]

Hello,

you need to relate the tables

and use something like Absolute Error:= ABS(SUMX(Actuals;Actuals[Actuals]-Related('L2 forecasts'[Forecast Qty]))).