Forum Discussion
Conditional Format for metrics
I created a database where I do the monthly update of some KPIs where I work.
I created a visualization using a matrix, where I show the platform, the current value, the goal and the performance.
My doubts:
- How do I create a conditional format for the performance value, that when <= 0 the background is red? And when it is > 0 the background is green? I made a rule that when the value is between -1000000000 and 0, the background turns red. It worked ok, it applies well in the performance line, but if Actual is 0, it also paints red. And if I make a rule to paint the values> 0 green, both the current and the goal are also painted green. And I would like to leave only the performance line painted.
- In this same matrix, is it possible to have the total of all platforms added? I ask this because the subtotals are adding all the lines. I would need something like:
- Platform1
- Actual = 100
- Goal = 50
- Performance = 50
- Platform2
- Actual = 150
- Goal = 150
- Performance = 0
- Total
- Actual = 250
- Goal = 200
- Performance = 50
- Platform1
If the database is not in the best shape, I accept suggestions on how to improve it too.
Best Regards,
Hi Anonymous ,
First you can create one meaure "background color" as below to judge the background color:(just for example as field "fev/20")
background color = var a=MAX('Plan1'[fev/20]) return SWITCH(TRUE(),a<=0&&MAX('Plan1'[A/G/P])="Performance","Red",a>0&&MAX('Plan1'[A/G/P])="Performance","Green")Then create matrix visual, drag the fields Platform, A/C/P and fev/20 onto visual Rows,Columns and Values fields
create measure and matrix
Select the martix visual, then go to Format tab, and under Conditional formatting, select "fev/20" as the column. Set the Background color to On, click "Advanced controls". It will open another window, set the Format By to Field Value, and then select the Background Color measure using the created"background color". then click on OK.
conditional formatting
You can refer this documentation about the condition formatting with background color.
Best Regards
Rena
4 Replies
For your question, you can't have a CF applied in different levels of your matrix BUT you can hack it.
The CF is depending not necessarily on the value visibile on the cell, so you can calculate a different metric that calculates the exact value if it's a performance, while if it's not it will return -1 or blank. Then you use that value as the conditional formatting rule.For your second question I dont' think you can have a hierarchy for the total, unless you calculate the "Total" as a calculated column, like it was a "category" like Platform 1 and 2. Or you use a different visual for the totals - it would be clearer.
You can create a measure that returns color based on the parameter of measure
Color Category = SWITCH(true(), Max(Sheet1[Category]) in {"A"} && Sheet1[Target]<10,"green", Max(Sheet1[Category]) in {"B"} && Sheet1[Target]<10,"orange" "red" )Then you can use field formatting under advance control conditional formatting : https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
Could you explain it better amitchandak ? I was unable to apply the formula to my base.
My fields turned orange.
Hi Anonymous ,
First you can create one meaure "background color" as below to judge the background color:(just for example as field "fev/20")
background color = var a=MAX('Plan1'[fev/20]) return SWITCH(TRUE(),a<=0&&MAX('Plan1'[A/G/P])="Performance","Red",a>0&&MAX('Plan1'[A/G/P])="Performance","Green")Then create matrix visual, drag the fields Platform, A/C/P and fev/20 onto visual Rows,Columns and Values fields
create measure and matrix
Select the martix visual, then go to Format tab, and under Conditional formatting, select "fev/20" as the column. Set the Background color to On, click "Advanced controls". It will open another window, set the Format By to Field Value, and then select the Background Color measure using the created"background color". then click on OK.
conditional formatting
You can refer this documentation about the condition formatting with background color.
Best Regards
Rena
