How can I generate dynamic change between 2 values in 2 separate tables

[thomaswhitemend]

Hello,
I have what appears to be a simple calculation that I am struggling with.

I've simplified this example to represent what I am trying to do. For security reasons I cannot share my file.
I have 3 distinct tables, Alpha, Bravo, and Charlie. Each table has an ID and Num column. While the ID values are the same, the Num values are different.

I am selecting 2 tables and visualizing them in a table in my report, represented in the 'Selection' cells in my linked image. I can currently display the highlighted green area in my image, and those values change based on my selections.
I would like to include another dynamic column as the blue area in my image. When the selection changes, I would like the % value to be recalculated based on that change. I am struggling to write a measure that I can use for this column, as I have too many records in my database (+10,000) to store comparisons of each individually.

As pseudocode I am looking for the following:
Change = Selection 1 Num / Selection 2 Num

Thank you for your assistance.

[v-yueyunzh-msft · Accepted solution]

Hi , @thomaswhitemend 

According to your description, you want to "generate dynamic change between 2 values in 2 separate tables".

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can click "New Table" to  create two slicer table :

Slicer 1 = {"A","B","C"}

Slicer 2 = {"A","B","C"}

(3)We need to click "New Table" to create a row header :

Row = DISTINCT( UNION( VALUES('Table A'[ID]) , VALUES('Table B'[ID]) , VALUES('Table C'[ID])))

(4)Then we can click "New Measure" to create three measures:

Slicer 1 Num = var _slier = MAX('Slicer 1'[Slicer1])
var _row = VALUES('Row'[ID])
return
SWITCH( _slier,
"A",CALCULATE( SUM('Table A'[Num]) , TREATAS(_row,'Table A'[ID])),
"B",CALCULATE( SUM('Table B'[Num]) , TREATAS(_row,'Table B'[ID])),
"C",CALCULATE( SUM('Table C'[Num]) , TREATAS(_row,'Table C'[ID])))

Slicer 2 Num = var _slier = MAX('Slicer 2'[Slicer2])
var _row = VALUES('Row'[ID])
return
SWITCH( _slier,
"A",CALCULATE( SUM('Table A'[Num]) , TREATAS(_row,'Table A'[ID])),
"B",CALCULATE( SUM('Table B'[Num]) , TREATAS(_row,'Table B'[ID])),
"C",CALCULATE( SUM('Table C'[Num]) , TREATAS(_row,'Table C'[ID])))

Change = [Slicer 1 Num]/[Slicer 2 Num]

(5)Then we can put the fields on the visual and we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[thomaswhitemend]

Thank you @v-yueyunzh-msft , as well as @Nathaniel_C. Your answers provided me with some insight as to how to solve my problem.

Specifically, from @v-yueyunzh-msft ,

Change = [Slicer 1 Num]/[Slicer 2 Num]

This example was able to point me to hooking into my selection values to create a new column in my visualization. My measure to solve the problem is as follows:

ChangeDiff = SELECTEDVALUE('Selection 1'[Num]) - SELECTEDVALUE('Selection 2'[Num])

 I have manipulated this also using the DIVIDE function to get %:

ChangeDiffPercent = DIVIDE(SELECTEDVALUE('Selection 1'[Num]), SELECTEDVALUE('Selection 2'[Num])​)

@lbendlin 

My reasoning for not creating another table to store these values was that the example tables 'Alpha, Bravo, Charlie' are only 3 'point-in-time' snapshots of values, and as such the list of tables can grow indefinitely as more are captured. In this case, I concluded storing a comparison of each number would lead to exponential growth in data storage, so it was best to calculate the changes between 'Selection 1'[Num] and 'Selection 2'[Num] as needed.

[Nathaniel_C]

Hi @thomaswhitemend , @lbendlin 

Is this what you had in mind?

Notice that the columns that are displaying change based onn the slicer selection.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @thomaswhitemend , @lbendlin ,
This is done with 3 tables as you indicated using the values you indicated.

This is done with a new functionality which is in preview mode - Power BI releases new updates frequently, and sometimes those updates are only available as a preview feature. In other words, the feature is not ready for complete release but it will work in most situations.
https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

My measure is called Dif (Percentage might be a better name.)

Dif = 

SWITCH((True),
And([SelectionValue1] = 0,[SelectionValue2]=0),DIVIDE(MAX(Alpha[Num]),MAX(Alpha[Num])),
And([SelectionValue1] = 0,[SelectionValue2]=1),DIVIDE(MAX(Alpha[Num]),MAX(Bravo[Num])),
And([SelectionValue1] = 0,[SelectionValue2]=2),DIVIDE(MAX(Alpha[Num]),MAX(Charlie[Num])),
And([SelectionValue1] = 1,[SelectionValue2]=0),DIVIDE(MAX(Bravo[Num]),MAX(Alpha[Num])),
And([SelectionValue1] = 1,[SelectionValue2]=1),DIVIDE(MAX(Bravo[Num]),MAX(Bravo[Num])),
And([SelectionValue1] = 1,[SelectionValue2]=2),DIVIDE(MAX(Bravo[Num]),MAX(Charlie[Num])),
And([SelectionValue1] = 2,[SelectionValue2]=0),DIVIDE(MAX(Charlie[Num]),MAX(Alpha[Num])),
And([SelectionValue1] = 2,[SelectionValue2]=1),DIVIDE(MAX(Charlie[Num]),MAX(Bravo[Num])),
And([SelectionValue1] = 2,[SelectionValue2]=2),DIVIDE(MAX(Charlie[Num]),MAX(Charlie[Num])),

100)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[lbendlin]

What's the rationale for using separate tables?  This would be much easier if all values were in the same table with a group attribute column (Alpha/Bravo/Charlie etc)