Variance Column

[Anonymous]

I have a matrix set up like this

company           2023           2024 4+8       2024 5+7

Orders

Sales

OI

ROS

But I want a variance column measuring the difference from 5+7 to 4+8. I would want it to look like this 

company           2023           2024 4+8       2024 5+7       Variance

Orders

Sales

OI

ROS

Does anyone know how I should approach this? I want the variance column to calculate each metric.

[PBIdashboards]

The accepted DAX solution works, but note that every time you add a new metric (Orders, Sales, OI, ROS like in your case) you need to duplicate the entire measure set. With 4 metrics that's already 8+ measures to maintain.

An alternative worth knowing: Flexa Tables (AppSource) lets you add a variance column between any two periods directly in the published report — no DAX, no Desktop access. End users select the two periods themselves. Works well for exactly this Actuals vs Plan matrix layout.

[danextian · Accepted solution]

Hi @Anonymous ,

You will need a disconnected table and some advanced DAX for that. In the attached example,  In-Progress is not a row in  Brand Sold column but in Status but by using a disconnected table and measures, i've made it appear so.

Please see attached pbix for your reference.