cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WJ876400 Helper III
Helper III

Varience

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

 

TargetActualVariance
1.171.85 
8.515.8 
8.528 
1.220.75 
8.528.2 
2.280.5 
4.872.6 

 

 

What calculation would I use to create the variance column?

 

thanks

1 ACCEPTED SOLUTION

Accepted Solutions
neatdot
Regular Visitor

Re: 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.

View solution in original post

5 REPLIES 5
neatdot
Regular Visitor

Re: 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.

View solution in original post

WJ876400 Helper III
Helper III

Re: Varience

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

neatdot
Regular Visitor

Re: Varience

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.

WJ876400 Helper III
Helper III

Re: Varience

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?

 

 

 

 

neatdot
Regular Visitor

Re: Varience

mTarget = SUM ( VALUE ( Table[Target] ) )

 

etc.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors