cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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

1 ACCEPTED SOLUTION
Community Support

Hi @sebbyp,

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

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
6 REPLIES 6
Community Support

Hi @sebbyp,

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

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper III

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.

Community Support

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 =
VAR current_date =
MAX ( Table2[Date] )
VAR total_amount =
SUMX (
FILTER (
ALL ( Table1 ),
&& 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper III

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

Many thanks

Community Support

Hi @sebbyp,

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

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper III

@v-shex-msftThank, just ordered it!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors