Matrix with of user-selected forecasts and multi-level differences

[JohnLederle]

Hello All,

Wish I could say I was brand new to PowerBI, but I just can't figure this one out.  

We generate weekly forecasts of a dozen or so rates. I need to create a matrix visual showing the forecast dates, values from two user-selected forecasts, and differences between individual rates.

For example, if the user wants to compare Forecast2 to Forecast3, the end product should look something like:

In the actual problem, there are dozens of forecasts from which to choose, and the selections do not have to be from consecutive weeks. Data is currently in a four-column table (sample data below --- apologies, tried to follow the recommended format). 

My specific question is how to calculate differences between the individual rates on the fly and include those differences in the matrix with the forecast rates.

Thank you for any help or advice.

JLed

ForecastName	RateName	Month	Value
FC2	Rate1	'Jan24	8.7
FC2	Rate1	'Feb24	8.6
FC2	Rate1	'Mar24	8.3
FC2	Rate1	'Apr24	8.0
FC2	Rate1	'May24	7.8
FC2	Rate1	'Jun24	7.9
FC2	Rate1	'Jul24	8.6
FC2	Rate1	'Aug24	8.3
FC2	Rate1	'Sep24	8.5
FC2	Rate1	'Oct24	8.7
FC2	Rate1	'Nov24	8.6
FC2	Rate1	'Dec24	7.8
FC2	Rate2	'Jan24	8.8
FC2	Rate2	'Feb24	9.0
FC2	Rate2	'Mar24	8.8
FC2	Rate2	'Apr24	9.4
FC2	Rate2	'May24	8.8
FC2	Rate2	'Jun24	8.3
FC2	Rate2	'Jul24	8.8
FC2	Rate2	'Aug24	8.4
FC2	Rate2	'Sep24	8.4
FC2	Rate2	'Oct24	7.9
FC2	Rate2	'Nov24	8.5
FC2	Rate2	'Dec24	7.9
FC2	Rate3	'Jan24	11.6
FC2	Rate3	'Feb24	11.5
FC2	Rate3	'Mar24	11.4
FC2	Rate3	'Apr24	11.0
FC2	Rate3	'May24	11.3
FC2	Rate3	'Jun24	10.8
FC2	Rate3	'Jul24	11.2
FC2	Rate3	'Aug24	10.4
FC2	Rate3	'Sep24	11.1
FC2	Rate3	'Oct24	11.2
FC2	Rate3	'Nov24	10.8
FC2	Rate3	'Dec24	10.3
FC3	Rate1	'Jan24	8.1
FC3	Rate1	'Feb24	8.4
FC3	Rate1	'Mar24	8.4
FC3	Rate1	'Apr24	8.2
FC3	Rate1	'May24	8.4
FC3	Rate1	'Jun24	7.9
FC3	Rate1	'Jul24	8.0
FC3	Rate1	'Aug24	8.5
FC3	Rate1	'Sep24	8.4
FC3	Rate1	'Oct24	8.2
FC3	Rate1	'Nov24	8.4
FC3	Rate1	'Dec24	7.8
FC3	Rate2	'Jan24	7.7
FC3	Rate2	'Feb24	8.1
FC3	Rate2	'Mar24	7.8
FC3	Rate2	'Apr24	7.8
FC3	Rate2	'May24	8.0
FC3	Rate2	'Jun24	8.0
FC3	Rate2	'Jul24	7.7
FC3	Rate2	'Aug24	7.8
FC3	Rate2	'Sep24	7.2
FC3	Rate2	'Oct24	7.2
FC3	Rate2	'Nov24	7.3
FC3	Rate2	'Dec24	7.4
FC3	Rate3	'Jan24	10.2
FC3	Rate3	'Feb24	10.9
FC3	Rate3	'Mar24	10.8
FC3	Rate3	'Apr24	10.1
FC3	Rate3	'May24	9.8
FC3	Rate3	'Jun24	9.8
FC3	Rate3	'Jul24	10.3
FC3	Rate3	'Aug24	10.2
FC3	Rate3	'Sep24	9.9
FC3	Rate3	'Oct24	10.5
FC3	Rate3	'Nov24	10.1
FC3	Rate3	'Dec24	10.6

[jgeddes · Accepted solution]

I am not sure you can get the exact visualization you are looking for but you can get something like this...

You would need to create two tables of distinct values of 'Forecast Name'. One table for each of the selection slicers.
From there you can create three measures.

Selection One = 
SUMX(
    FILTER(rateTable, rateTable[ForecastName ] = SELECTEDVALUE(selectionTableOne[ForecastName ])),
    rateTable[Value ]
)

Selection Two = 
SUMX(
    FILTER(rateTable, rateTable[ForecastName ] = SELECTEDVALUE(selectionTableTwo[ForecastName ])),
    rateTable[Value ]
)

Selection Difference = 
[Selection One] - [Selection Two]

You do not need to create any relationships between the tables. 

[JohnLederle]

Thanks jgeddes for your fast and helpful response! You saved me all kinds of time. I wish Power BI was capable of showing the results in the desired order, but most important was to get the numbers published in a report.