Forum Discussion
Challenging Measure Calculation
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 |
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
6 Replies
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
AnonymousThats 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.
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:
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
