Conditional formatting based on slicer value

[Anonymous]

I have a matrix that is a budget to actual comparision. My variance column is a meausre that i created. Then i have months data rolling into this depenidng on the month that is slected on the slicer. The fiscal year starts July so that month number is 1. 

What i am trying to do it have the variance column highlight red if over the expected amount. The expected amount would simply be caulculated like if the slicer is ending in October (#4 month) then the excepted amount would be 4/12 or 33%. So if that percentage in the variance column is above 33% then it would be highlighted red. See below for pictures.

matrixslicer

Any suggestions is greatly appreciated. thanks!

[v-lili6-msft · Accepted solution]

hi, @Anonymous

After my research, you could do these as below:

Step1:

this is variance measure

variance = CALCULATE(SUM(Table1[Revenue]))/CALCULATE(SUM(Table1[Revenue Budget]))

Then we add two more other measures

Number months = CALCULATE((DATEDIFF(MIN(Table1[Date]),MAX(Table1[Date]),MONTH)+1)/12,ALLEXCEPT(Table1,Table1[Date]))

Conditional = IF([variance]>[Number months],1,2)

Step2:

select matrix visual and right-click field variance, Conditional formatting ->Background color

Step3:

Select Format by Rules -> Based on field measure Conditional ->Rules if value is 1

Result:

here is my demo, please try it.

https://www.dropbox.com/s/j4h35fcpyvu7jir/Conditional%20formatting%20based%20on%20slicer%20value.pbi...

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft

Thank you! That works great.  And thanks for the indepth step by step. It was the nice finishing touch to the report.