Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have two columns, a target and actual. I want to create another column which is a calculation and then base some conditional formatting off the new column.
For the new column variance, these are the parameters -
Red – more than 1% behind target
Amber – within 1% of target
Green – on target or exceeding target
Target | Actual | Variance |
1.17 | 1.85 | |
8.5 | 15.8 | |
8.52 | 8 | |
1.22 | 0.75 | |
8.52 | 8.2 | |
2.28 | 0.5 | |
4.87 | 2.6 |
What calculation would I use to create the variance column?
thanks
Solved! Go to Solution.
In Power Query:
= Table.AddColumn(<previous step>, "Variance", each ( [Actual] - [Target] ) / [Target])
Or in DAX:
Variance = DIVIDE ( Table[Actual] - Table[Target], Table[Target] )
In the DAX version, remember to replace Table with your actual table name.
In the visual you base off the column, you will need to use conditional formatting to apply a colour based on the value of Variance.
However, it's worth pointing out that a more optimal solution would use measures. You should create measures for Target and Actual, then a measure for Variance (rather than a calculated column). The reason for this is that calculated columns take up space in your data model, and also may not produce the correct results depending on how thay are aggregated. A measure will (iff well designed) always produce the correct answer no matter how you aggregate.
In Power Query:
= Table.AddColumn(<previous step>, "Variance", each ( [Actual] - [Target] ) / [Target])
Or in DAX:
Variance = DIVIDE ( Table[Actual] - Table[Target], Table[Target] )
In the DAX version, remember to replace Table with your actual table name.
In the visual you base off the column, you will need to use conditional formatting to apply a colour based on the value of Variance.
However, it's worth pointing out that a more optimal solution would use measures. You should create measures for Target and Actual, then a measure for Variance (rather than a calculated column). The reason for this is that calculated columns take up space in your data model, and also may not produce the correct results depending on how thay are aggregated. A measure will (iff well designed) always produce the correct answer no matter how you aggregate.
Hi @neatdot
Thanks for the quick response I will try the below. If I do it as a measure do i use the same calculation as below?
thanks
Try this for the measures:
mTarget = SUM ( Table[Target] )
mActual = SUM ( Table[Actual] )
Variance = DIVIDE ( mActual - mTarget, mTarget )
If you want to preserve the names Target and Actual for the measures, I normally rename the underlying columns with an underscore ('_Target' and '_Actual'), then hide the original columns in the report interface. Then you can use Target, Actual and Variance in your report visuals.
Hi @neatdot
thanks for this, sorry the target field is text as it comes from another formula, is there something extra I need to do in the formula?
mTarget = SUM ( VALUE ( Table[Target] ) )
etc.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |