Dynamic calculations over several tables

[TobiasV]

Hallo everyone,

i have a tricky problem which i cannot solve on my own and i hope you can help me. 🙂

I have three Tables (two of them are connected with a Key):

Table 1:

Key	Value_before
1	20
2	30
3	40

Table 2:

Key	Value_after
1	20
2	40
3	0

Table3:

i

0,05

0,1

I would like to visualize the following value:

Delta = MAX(0, Value_After - Value_Before * (1+i) )

and the user has the option to set the value of i to 0,05 or 0,1 with a slicer.

For example, if i want to display the overall sum of Delta (with slicer for i = 0,1) it should show the following value: 

MAX(0, 20-22) + MAX(0, 40-33) + MAX(0, 0-44) = 7

Is there a way to do this with a meassure? 

Thank you for your help!

Best wishes

Tobias

[v-zhenbw-msft · Accepted solution]

Hi @TobiasV ,

We assume you have a one-to-one relationship between Table 1 and Table 2.

Then we can create a measure to meet your requirement.

Measure = 
VAR _select_i =
    SELECTEDVALUE ( 'Slicer table'[i], 0 )
RETURN
    SUMX (
        VALUES ( 'Table 1'[Key] ),
        MAX (
            0,
            CALCULATE ( SUM ( 'Table 2'[Value_after] ) )
                - CALCULATE ( SUM ( 'Table 1'[Value_before] ) ) * ( 1 + _select_i )
        )
    )

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[parry2k]

@TobiasV how table 1 and 2 are related? Is it 1:1 relationship or 1 to many ?