Forum Discussion

Reits's avatar
Reits
Regular Visitor
8 years ago
Solved

Sumx calculation per row between 2 tables

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?

 

  • Reits's avatar
    Reits
    8 years ago

    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 :)

3 Replies

  • Floriankx's avatar
    Floriankx
    Solution Sage
    8 years ago

    Hello,

     

    you need to relate the tables

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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Reits
      Regular Visitor
      8 years ago

      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 :)