Forum Discussion
Varience
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.