Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register 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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |