Variance in matrix table

[Anonymous]

I have the matrix table below. Each of the rows are measures - I've done this by the 'Switch Values to Rows' toggle. I'd like to calculate the variance between Actuals and Forecast is this possible?

My source data is as below. These only show the first few columns but essentially the remaining columns are just the other measures e.g. net sales, COGS

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a table.

then create a measure as follow：

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yalanwu-msft thank you for this, this is very helpful.

EDIT: I've managed to make this work. Is it possible to change the name of the Total comumn to variance?

Thanks

[Anonymous]

@amitchandak thanks - I don't quite understand what needs to be included in the new table? I want to be able to show the variance for each P&L item in a matrix, is this possible?

[amitchandak]

@Anonymous , Create a new table with values Actual, forecast and variance , Join it with that column ,

then create a measure like

if(Max(Dim[Actual Vs Forecast]) = "variance", calculate(Sum(Table[Value]), Dim[Actual Vs Forecast]="Actual") - calculate(Sum(Table[Value]), Dim[Actual Vs Forecast]="Forecast") , Sum(Table[Value]) )

use Dim's column [Actual Vs Forecast] in the visual