Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 slic...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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.pbix?dl=0

     

    Best Regards,

    Lin

     

     

     

     

     

     

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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.pbix?dl=0

 

Best Regards,

Lin

 

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.