Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sebbyp
Helper III
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  
DateTrade PartnerValue
15/01/2017US150
15/01/2017Japan20
15/01/2017China150
15/01/2017EU200
15/02/2017US200
15/02/2017Japan50
15/02/2017China100
15/02/2017EU150

 

Table 2    
DateCountryTrade PartnerTrade Partner WeightWeighted Value
15/01/2017USUS0.80120.00
15/01/2017USJapan0.102.00
15/01/2017USChina0.057.50
15/01/2017USEU0.1530.00
15/01/2017JapanUS0.2537.50
15/01/2017JapanJapan0.7014.00
15/01/2017JapanChina0.1522.50
15/01/2017JapanEU0.1020.00
15/01/2017China US0.2030.00
15/01/2017China Japan0.153.00
15/01/2017China China0.90135.00
15/01/2017China EU0.1530.00
15/01/2017EUUS0.2233.00
15/01/2017EUJapan0.122.40
15/01/2017EUChina0.1015.00
15/01/2017EUEU0.90180.00

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

3.PNG

 

Regards,

Xiaoxin Sheng

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

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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

3.PNG

 

Regards,

Xiaoxin Sheng

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

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

 

 

 

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

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

@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

Hi @sebbyp,

 

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

 

Regards,
Xiaoxin Sheng

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

@v-shex-msftThank, just ordered it!

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

Top Solution Authors