Calculating Variance

[Anonymous]

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?

[rocky09 · Accepted solution]

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.

[Anonymous]

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]

do you have a date column as well?

[Anonymous]

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

[rocky09]

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

[Anonymous]

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

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

[Anonymous]

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