Forum Discussion
Helper IVariance and Variance % between 2 measures
Hi there,
I have 2 measures which by itself does a lot of calculations. However, I need the Variance between those 2 measures, and the Variance % of those 2 measures. How I've done it is as below:
- Variance = [Measure 1] - [Measure 2]
- Variance % = [Measure 1]/[Measure 2] -1
These 4 measures are then on a matrix table.
I understand doing it this way means Measure 1 and Measure 2 will each be calculated 3 times, which is really slowing down the performance. Is there any ideas to make this quicker?
Thanks!
jessset
Or you can transform Measure 1 and Measure 2 into calculate columns, so it is pre-calculated and saved in the pbix, this would increase the pbix size but increase the performance.Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
jessset , I think you have done it the right way. The only change you can do is
Variance % = divide([Measure 1]-[Measure 2],[Measure 2] )
This can help to improve performance
jessset
Or you can transform Measure 1 and Measure 2 into calculate columns, so it is pre-calculated and saved in the pbix, this would increase the pbix size but increase the performance.Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PBIdashboardsPost Patron
Your approach is correct Variance = [Measure 1] - [Measure 2] and Variance % = DIVIDE([Measure 1] - [Measure 2], ABS([Measure 2])) (using DIVIDE instead of / avoids division-by-zero errors).
Worth noting for teams where this pattern keeps multiplying: once you have 5–6 metrics each needing their own Variance and Variance % measure, you end up with 15–18 measures to maintain. Any schema change ripples across all of them.
If the goal is to let end-users add variance columns themselves in the published report without the developer maintaining a growing measure list, Flexa Tables on AppSource handles this variance between any two columns is a one-click button in the published report, no DAX needed
