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

Calculating Variance

I have a table (see below) that we are hoping to look at the variance PER ROW CELL.  Meaning.... if the ACTUAL number has a >20%, then that CELL will turn YELLOW.  if you look at the table below, you will see a couple cells that the number is greater that 20% variance from the AGREED cell right next two it.  For example there are a couple cells in this screenshot that show 4 and 6.  the respect Actual cell next to each is 0 so they would turn YELLOW.  

 

We do not want to compare all the data as a collective but each row's two cells to each other (Agreed vs Actual).  Can someone tell me how to make the Actual Cell turn yellow if its cell number has a greater variance of 20% vs its neighbors Agreed cell?

 

cnorris3570_0-1646415275018.png

 

1 ACCEPTED SOLUTION
rocky09
Solution Sage
Solution Sage

I hope, I understood your question properly.
Try this.
create a measure:

_variance = 
IF(
AVERAGEX(data,
        data[Monday Agreed] - data[Monday Actual]) > 2,"#F5E633","#fff")

Then, apply the conditional formatting of the column and select the measure.

rocky09_0-1646418765260.png

 

View solution in original post

7 REPLIES 7
rocky09
Solution Sage
Solution Sage

I hope, I understood your question properly.
Try this.
create a measure:

_variance = 
IF(
AVERAGEX(data,
        data[Monday Agreed] - data[Monday Actual]) > 2,"#F5E633","#fff")

Then, apply the conditional formatting of the column and select the measure.

rocky09_0-1646418765260.png

 

okay for Tuesday ......... thru Saturday, how is the code written as i have columns for Tuesday Agreed, Tuesday Actual....... Saturday Agreed, Saturday Actual in the same table?

_variance =
IF(
AVERAGEX(ManPowerTable,
ManPowerTable[Monday Agreed] - ManPowerTable[Monday Actual]) > 2,"#F5E633","#fff")




rocky09
Solution Sage
Solution Sage

do you have a date column as well?

i have a date table with a date column but  this table does not have a date column in it.

rocky09
Solution Sage
Solution Sage

are you trying to compare Agreed vs Actual? or variance between Agreed previous cell vs Agreed Current cell and the same with Actual?

im actually trying to compare Agreed previous cell vs Agreed Current cell...... like this:

cnorris3570_1-1646416069413.png

 

the 0 would turn yellow.   for each ROW that the second cell (Actual) is greater than the 20%, then it would turn yellow.  

so in that table i first posted, only two cells would have turned YELLOW.

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.