cancel
Showing results for
Did you mean:
New Member

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

 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?

Best wishes

Tobias

1 ACCEPTED SOLUTION
Community Support

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 2
Community Support

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.

Super User III

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!