Forum Discussion
Variance Column
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.
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.
2 Replies
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.
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.
