Forum Discussion
Conditional formatting based on slicer value
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!
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.
Best Regards,
Lin
2 Replies
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.
Best Regards,
Lin
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.