Multiply two measures from two different tables

[MCacc]

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

The two tables are built differently, even though they sort of represent the same thing logically. Let's say that the first table is divided into columns, the second into rows. 

I have this TABEL_1

NUMBER_A  --- NUMBER_B --- NUMBER_C --- NUMBER_D --- NUMBER_E

23                   |    25                |         45           |       67             |           89

*NUMBER_A, NUMBER_B, NUMBER_C, ... are just the numbers for my dimension that you'll find in the TABLE_2 as seen below:

This is TABLE_2

DIMENSION ---- NUMBER

A                     |        23

B                     |        25

C                     |        45

D                     |        67

E                      |        89

-------------------------------------------------------------------------------------------------------------------------------------------

Now... I have mad the measures for each column of TABLE_1:

-MEASURE_1 SUM(NUMBER_A)
-MEASURE_2 SUM(NUMBER_B)
-MEASURE_3 SUM(NUMBER_C)
-MEASURE_4 SUM(NUMBER_D)
MEASURE_5 SUM(NUMBER_E)
-------------------------------------------------------------------------------------------------------------------------------------

For the second table TABLE_2 I have only one measure:

MEASURE_TABLE2 = SUM(NUMBER) 

which is then "split" through its dimension "DIMENSION" once is places in a table visual in powerbi:

DIMENSIION ----------- MEASURE_TABLE_2 

A                             |              677

B                             |               899

C                             |               455

D                             |               678

E                              |               3454

The issue is, my final measure should be the multiplication between MEASURE 1 and MEASURE TABLE2 grouped by dimension A, then MEASURE 2 grouped by dimension B, ecc...

Something like:

MEASURE 1 * MEASURE TABLE2 (GROUP(A))

MEASURE 2 * MEASURE TABLE2 (GROUP(B))

MEASURE 3 * MEASURE TABLE2 (GROUP(C)), ecc....

Is it possible to achieve this with only one measure?

Thank you!

[MFelix · Accepted solution]

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