Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WJ876400
Helper IV
Helper IV

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
neatdot
Helper I
Helper I

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
Helper I
Helper I

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.