Forum Discussion

MCacc's avatar
MCacc
Helper IV
5 years ago
Solved

Multiply two measures from two different tables

Hello there, I have a problem implementing a measure logic for my project. What they're asking me is a multiplication between measures that come from two different (but related) tables: TABLE_1 and ...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi MCacc ,

     

    Believe that the most effective way to solve this would be to unpivot the table 1 columns and then make a dimension table to relate both table that way you could multiply the both measure directly using the dimension table.

     

    Since you have the information in the way yo are presenting you must create the following measure to have the multiplication:

    TotalMeasureByDimension = 
SUMX (
    Table_2;
    [MeasureTable2]
        * SWITCH (
            LASTNONBLANK ( Table_2[Dimension]; 0 );
            "A"; [Measure1];
            "B"; [Measure2];
            "C"; [Measure3];
            "D"; [Measure4];
            "E"; [Measure5]
        )
)

     

     

MFelix's avatar
MFelix
Super User
5 years ago

Hi MCacc ,

 

Believe that the most effective way to solve this would be to unpivot the table 1 columns and then make a dimension table to relate both table that way you could multiply the both measure directly using the dimension table.

 

Since you have the information in the way yo are presenting you must create the following measure to have the multiplication:

TotalMeasureByDimension = 
SUMX (
    Table_2;
    [MeasureTable2]
        * SWITCH (
            LASTNONBLANK ( Table_2[Dimension]; 0 );
            "A"; [Measure1];
            "B"; [Measure2];
            "C"; [Measure3];
            "D"; [Measure4];
            "E"; [Measure5]
        )
)