Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I want to colour the background of a cell in my table based on a rank (gradient where: 1= green, middle value = yellow, highest value = red). However, there are 5 different groups of different sizes that could appear in this table, so I can't simply assign red to the highest value because one group could have a range of 1-50, another 1-80, and another 1-110.
If I set red to the highest value, it changes based on filters, and I do not want the colouring to change when the table is filtered (if I wanted to see one row at a time, I want the rank colouring to remain the same as when unfiltered).
Is there any way I can set conditional formatting but have these colours assigned somehow based on a background condition? Like if group = Group A, the gradient colour range is 1,50,100 but if group = Group B, the gradient colour range is 1,25,50 for the same column conditional background formatting in a table?
Solved! Go to Solution.
Hi, @madison13g
Thanks for @PhilipTreacy reply. Based on your description, you can refer to Measure below.
Steps:
1. Choose the visual, turn to Cell elements and open Backgroud color.
2. Use Field value and choose the measure.
Measure:
Group color =
VAR _group1 =
CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group1", ALL ( 'Table' ) )
VAR _group2 =
CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group2", ALL ( 'Table' ) )
VAR _group3 =
CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group3", ALL ( 'Table' ) )
VAR _group =
SELECTEDVALUE ( 'Table'[Group] )
VAR _value =
SELECTEDVALUE ( 'Table'[value] )
RETURN
// _group1
SWITCH (
_group,
"Group1",
IF (
_value = 1,
"Green",
IF ( _value > 1 && _value < _group1, "Yellow", IF ( _value = _group1, "Red" ) )
),
"Group2",
IF (
_value = 1,
"Green",
IF ( _value = _group2, "Red", IF ( _value > 1 && _value < _group2, "Yellow" ) )
),
"Group3",
IF (
_value = 1,
"Green",
IF ( _value = _group3, "Red", IF ( _value > 1 && _value < _group3, "Yellow" ) )
)
)
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, @madison13g
Thanks for @PhilipTreacy reply. Based on your description, you can refer to Measure below.
Steps:
1. Choose the visual, turn to Cell elements and open Backgroud color.
2. Use Field value and choose the measure.
Measure:
Group color =
VAR _group1 =
CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group1", ALL ( 'Table' ) )
VAR _group2 =
CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group2", ALL ( 'Table' ) )
VAR _group3 =
CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group3", ALL ( 'Table' ) )
VAR _group =
SELECTEDVALUE ( 'Table'[Group] )
VAR _value =
SELECTEDVALUE ( 'Table'[value] )
RETURN
// _group1
SWITCH (
_group,
"Group1",
IF (
_value = 1,
"Green",
IF ( _value > 1 && _value < _group1, "Yellow", IF ( _value = _group1, "Red" ) )
),
"Group2",
IF (
_value = 1,
"Green",
IF ( _value = _group2, "Red", IF ( _value > 1 && _value < _group2, "Yellow" ) )
),
"Group3",
IF (
_value = 1,
"Green",
IF ( _value = _group3, "Red", IF ( _value > 1 && _value < _group3, "Yellow" ) )
)
)
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Please provide some data we can work with.
Regards
Phil
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |