New column as sum of two columns from ellements in different rows

[Anonymous]

Hello,

this requirement is kind of hard to explain but I will give my best to illustate it.

I do have two tables:

1) Table A with two columns: (i) Column Index which is basicaly a unique integer and (ii) column Size which is a decimal number. Table A has 100 rows. Index goes from 100 to 200.

2) Table B with two columns: (1) Column Index which is basicaly a unique integer and (ii) column Size which is a decimal number. Table B has 300 rows. Index goes from 1-300.

I want to create a calculated column in Table A Total that each row (k) will be the result of A.Size(k)+B.Size(k+z) where z is an integer between -50 and 50.

Is this somehow possible via a calculated column either in the query or in the report level? idealy the z should be selectable via a visualization but this is not a must.

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Create a measure instead of column in the table A.

Total = SUM(TableA[Size])+CALCULATE(SUM(TableB[Size]),FILTER(TableB,TableB[Index]=MAX(TableA[Index])+MAX(TableZ[z])))



Regards,
Lydia

[Anonymous]

I am trying to understand why it works but I cant. I need to understand what exactly the measure does. It is awesome though befcause even if it reached the boundaries it does what I wanted it to do!

Just to help others in the future it can be used to "move" plots left and right. Thank you @v-yuezhe-msft!