DAX Related / SUMX

[lukmtb08]

Dear community, 

my issue: I have two tables with a many-to-one relationship: 
T1

Data Type	Value	Month	key exchange rate
YTD	1000
YTD	2000
YTD	...
Forecase	3000
Forecast	2000
Forecast	...

T2

key exchange rate	value
...	...

I would like to calculate: (Amount Forecast - Amount YTD) * Value exchange rate

The value of the excahnge rate should be the one for forecast. I always calculate on a monthly basis, so I have just one value for all the forecast rows. The functionality of the related function with related is clear. But i do not understand how to solve my issue as I first have to calculate with more rows and in last step, with just one uniqe value. 

Thank you for your support. 

[v-yadongf-msft · Accepted solution]

Hi @lukmtb08 ,

This is my test table 1:

Test table 2:

Relationship:

Create a new column:

Column =
VAR sum_ytd =
    CALCULATE (
        SUM ( 'Table1'[Value] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Data Type] = "YTD" )
    )
VAR sum_forecast =
    CALCULATE (
        SUM ( 'Table1'[Value] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Data Type] = "Forecast" )
    )
VAR _value =
    MAXX (
        FILTER ( 'Table1', 'Table1'[Data Type] = "Forecast" ),
        RELATED ( 'Table2'[value] )
    )
RETURN
    ( sum_ytd - sum_forecast ) * _value

Is this the result you want? Please refer the attached pbix file.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lukmtb08]

Thank you very much! 🙂

[amitchandak]

@lukmtb08 , There few ways to copy data from one table to another

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8