Challenging Measure Calculation

[sebbyp]

Help Please on this challenging measure calculation.  I have two tables and i would like to create the column or measure for the 'Weighted Value' in Table 2.  The calculation is Table 2 Trade Partner Weight * Table 1 Trade Partner Value.

Any ideas how i can do this? 

Table 1
Date	Trade Partner	Value
15/01/2017	US	150
15/01/2017	Japan	20
15/01/2017	China	150
15/01/2017	EU	200
15/02/2017	US	200
15/02/2017	Japan	50
15/02/2017	China	100
15/02/2017	EU	150

Table 2
Date	Country	Trade Partner	Trade Partner Weight	Weighted Value
15/01/2017	US	US	0.80	120.00
15/01/2017	US	Japan	0.10	2.00
15/01/2017	US	China	0.05	7.50
15/01/2017	US	EU	0.15	30.00
15/01/2017	Japan	US	0.25	37.50
15/01/2017	Japan	Japan	0.70	14.00
15/01/2017	Japan	China	0.15	22.50
15/01/2017	Japan	EU	0.10	20.00
15/01/2017	China	US	0.20	30.00
15/01/2017	China	Japan	0.15	3.00
15/01/2017	China	China	0.90	135.00
15/01/2017	China	EU	0.15	30.00
15/01/2017	EU	US	0.22	33.00
15/01/2017	EU	Japan	0.12	2.40
15/01/2017	EU	China	0.10	15.00
15/01/2017	EU	EU	0.90	180.00

[v-shex-msft · Accepted solution]

Hi @sebbyp,

You can try to use below formual to get the weighted value(calculate column):

Weighted Value =
[Trade Partner Weight]
    * SUMX (
        FILTER (
            ALL ( Table1 ),
            Table1[Trade Partner] = EARLIER ( Table2[Trade Partner] )
                && Table1[Date] = EARLIER ( Table2[Date] )
        ),
        Table1[Value]
    )

Regards,

Xiaoxin Sheng

[sebbyp]

@v-shex-msftThats super awesome.  It has worked perfectly.  If you don't mind, can i ask you a couple of things about this calculation.

1) Why did you use calculate column instead of Measure.  I thought measure would be appropirate but clearly it isn't.

2) Would you mind describing the calculation so i can understand the structure of the formula and what it is doing?  In particular i don't understand the filter part.

[v-shex-msft]

Hi @sebbyp,

>>1) Why did you use calculate column instead of Measure.  I thought measure would be appropirate but clearly it isn't.

Actually, calculated column is more simple to get current row contents and can simply to show the result.

Measure also support these calculation, below is the formula:

Weighted Value =
VAR current_weight =
    MAX ( Table2[Trade Partner Weight] )
VAR current_partener =
    LASTNONBLANK ( Table2[Trade Partner], [[Trade Partner] )
VAR current_date =
    MAX ( Table2[Date] )
VAR total_amount =
    SUMX (
        FILTER (
            ALL ( Table1 ),
            Table1[Trade Partner] = current_partener
                && Table1[Date] = current_date
        ),
        Table1[Value]
    )
RETURN
    current_weight * total_amount

>>2) Would you mind describing the calculation so i can understand the structure of the formula and what it is doing?  In particular i don't understand the filter part.

For that formula, the key point is find out current calculation row contents and use it as the parameter to filter on other table.

Reference:

EARLIER Function (DAX)

Returns the current value of the specified column in an outer evaluation pass of the mentioned column.
EARLIER is useful for nested calculations where you want to use a certain value as an input and produce calculations based on that input. In Microsoft Excel, you can do such calculations only within the context of the current row; however, in DAX you can store the value of the input and then make calculation using data from the entire table.
EARLIER is mostly used in the context of calculated columns.

Row Context, Nested Functions, and EARLIER() in PowerPivot and DAX

Regards,

Xiaoxin Sheng

[sebbyp]

@v-shex-msftthanks very much.  You have given a very clear and helpful explanation.

I would like to learn more about these formulas and what the benefits and disadvantages are.  Can you recommend a book which i can read about this.

Many thanks

[v-shex-msft]

Hi @sebbyp,

Maybe you can take a look at The Definitive Guide to DAX, I also learn from this book.

Regards,
Xiaoxin Sheng

[sebbyp]

@v-shex-msftThank, just ordered it!