Forum Discussion

TobiasV's avatar
TobiasV
New Member
6 years ago
Solved

Dynamic calculations over several tables

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:

KeyValue_before
120
230
340

 

Table 2:

KeyValue_after
120
240
30

 

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

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

2 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.