Hi all, This is probably an easy one to fix, but I currently have a table that shows the houses and the score and I would like to format the house colour based on where they sit in the quartiles calculated below. Quartile % Measure =VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG])RETURNSWITCH( TRUE(), score >= 0.25, "#F3747E", score > 0.25 && score <= .5, "#F2EC4B", score > .5 && score <=.75, "#CCFFA0", "#A0D1FF") However when I try to format the house based on this measure it returns "#F3747E" House SCORE Colour based on QuartileHouse 1 26 #F3747EHouse 2 42 #F2EC4BHouse 3 80 #CCFFA0Hope this makes sense Thanks

Anonymous , Try like Quartile % Measure =VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG])RETURNSWITCH(TRUE(),score <= 0.25, "#F3747E",score <= .5, "#F2EC4B",score <=.75, "#CCFFA0","#A0D1FF")You can use same in conditional formatting with the field value option

Forum Discussion

Anonymous

Not applicable

9 months ago

Solved

Conditional Formatting based on Quartile % against score

Hi all,

This is probably an easy one to fix, but I currently have a table that shows the houses and the score and I would like to format the house colour based on where they sit in the quartiles calculated below.

Quartile % Measure =

VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG])

RETURN

SWITCH(

TRUE(),

score >= 0.25, "#F3747E",

score > 0.25 && score <= .5, "#F2EC4B",

score > .5 && score <=.75, "#CCFFA0",

"#A0D1FF"

)

However when I try to format the house based on this measure it returns "#F3747E"

House SCORE Colour based on Quartile

House 1 26 #F3747E

House 2 42 #F2EC4B
House 3 80 #CCFFA0

Hope this makes sense

Thanks

Need Help

amitchandak
9 months ago
Anonymous , Try like

Quartile % Measure =
VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG])
RETURN
SWITCH(
TRUE(),
score <= 0.25, "#F3747E",
score <= .5, "#F2EC4B",
score <=.75, "#CCFFA0",
"#A0D1FF"
)

You can use same in conditional formatting with the field value option
Zanqueta
9 months ago
Hi Anonymous,

Your current measure always returns #F3747E because the first condition score >= 0.25 is always true for any value above 0.25. As a result, the SWITCH function stops evaluating further conditions and returns the first match.

Please, try it:

Quartile Colour = VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG]) RETURN SWITCH( TRUE(), score <= 0.25, "#F3747E", -- 1st quartile score <= 0.5, "#F2EC4B", -- 2nd quartile score <= 0.75, "#CCFFA0", -- 3rd quartile "#A0D1FF" -- 4th quartile )

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

wardy912

9 months ago

Hi Anonymous

You need to normalise the score first to achieve this. You have a couple of options

Option 1:

Normalise the score

Quartile % Measure =
VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG]) / 100
RETURN
SWITCH(
    TRUE(),
    score <= 0.25, "#F3747E",       // Bottom quartile
    score <= 0.50, "#F2EC4B",       // Second quartile
    score <= 0.75, "#CCFFA0",       // Third quartile
    "#A0D1FF"                        // Top quartile
)

Option 2:

Use actual quartile values

Quartile % Measure =
VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG])
VAR Q1 = PERCENTILEX.INC(ALL('Ranks Normalised'), [GM_TOTAL_BRAG], 0.25)
VAR Q2 = PERCENTILEX.INC(ALL('Ranks Normalised'), [GM_TOTAL_BRAG], 0.50)
VAR Q3 = PERCENTILEX.INC(ALL('Ranks Normalised'), [GM_TOTAL_BRAG], 0.75)
RETURN
SWITCH(
    TRUE(),
    score <= Q1, "#F3747E",
    score <= Q2, "#F2EC4B",
    score <= Q3, "#CCFFA0",
    "#A0D1FF"
)

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

wardy912

9 months ago

Hi Anonymous

Calculate the maximum score for the Area and then apply the quartile logic based on that maximum instead of the individual house score

Quartile Conditional Format Colour Area =
VAR AreaMaxScore =
    CALCULATE(
        MAX('Ranks Normalised'[BDM_ALL_RET_RANK_IN_LVL2]),
        ALLEXCEPT('Ranks Normalised', 'Ranks Normalised'[Area])
    )
VAR Q1 = PERCENTILEX.INC(ALL('Ranks Normalised'), [BDM_ALL_RET_RANK_IN_LVL2], 0.25)
VAR Q2 = PERCENTILEX.INC(ALL('Ranks Normalised'), [BDM_ALL_RET_RANK_IN_LVL2], 0.50)
VAR Q3 = PERCENTILEX.INC(ALL('Ranks Normalised'), [BDM_ALL_RET_RANK_IN_LVL2], 0.75)
RETURN
SWITCH(
    TRUE(),
    AreaMaxScore <= Q1, "#FAC9CD",   // Red
    AreaMaxScore <= Q2, "#F7F5B6",   // Yellow
    AreaMaxScore <= Q3, "#E6FAD6",   // Green
    "#DBEDFD"                        // Blue
)

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

7 Replies

amitchandak
Super User
9 months ago
Anonymous , Try like

Quartile % Measure =
VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG])
RETURN
SWITCH(
TRUE(),
score <= 0.25, "#F3747E",
score <= .5, "#F2EC4B",
score <=.75, "#CCFFA0",
"#A0D1FF"
)

You can use same in conditional formatting with the field value option
Zanqueta
Super User
9 months ago
Hi Anonymous,

Your current measure always returns #F3747E because the first condition score >= 0.25 is always true for any value above 0.25. As a result, the SWITCH function stops evaluating further conditions and returns the first match.

Please, try it:

Quartile Colour = VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG]) RETURN SWITCH( TRUE(), score <= 0.25, "#F3747E", -- 1st quartile score <= 0.5, "#F2EC4B", -- 2nd quartile score <= 0.75, "#CCFFA0", -- 3rd quartile "#A0D1FF" -- 4th quartile )

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

wardy912

Super User

9 months ago

Hi Anonymous

You need to normalise the score first to achieve this. You have a couple of options

Option 1:

Normalise the score

Quartile % Measure =
VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG]) / 100
RETURN
SWITCH(
    TRUE(),
    score <= 0.25, "#F3747E",       // Bottom quartile
    score <= 0.50, "#F2EC4B",       // Second quartile
    score <= 0.75, "#CCFFA0",       // Third quartile
    "#A0D1FF"                        // Top quartile
)

Option 2:

Use actual quartile values

Quartile % Measure =
VAR score = SELECTEDVALUE('Ranks Normalised'[GM_TOTAL_BRAG])
VAR Q1 = PERCENTILEX.INC(ALL('Ranks Normalised'), [GM_TOTAL_BRAG], 0.25)
VAR Q2 = PERCENTILEX.INC(ALL('Ranks Normalised'), [GM_TOTAL_BRAG], 0.50)
VAR Q3 = PERCENTILEX.INC(ALL('Ranks Normalised'), [GM_TOTAL_BRAG], 0.75)
RETURN
SWITCH(
    TRUE(),
    score <= Q1, "#F3747E",
    score <= Q2, "#F2EC4B",
    score <= Q3, "#CCFFA0",
    "#A0D1FF"
)

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

Anonymous
Not applicable
9 months ago
Thanks everyone for your replies, they all worked and I even found another way myself 🙂
Amazing how one issue can spark a number of ways to find an outcome
Anonymous
Not applicable
9 months ago
Hi all,
I have another issue now with this. It looks as though this is ok for the houses, however when I add the revised calculation at the parent level it returns multiple colours for the area.
I.e.
Max Value on Measure
Area1 - House 1 - 96 - Blue
House 2 - 96 - Blue
Area 2 - House 1 - 96 - Blue
House 2 - 43 - Red
It then return a red for Area 2 where it should be returning a blue because 96 is the maximum, any thoughts?
Am Using the below measure

Quartile Conditional Format Colour Area =
VAR score = SELECTEDVALUE('Ranks Normalised'[BDM_ALL_RET_RANK_IN_LVL2])
VAR Q1 = PERCENTILEX.INC(ALL('Ranks Normalised'), [BDM_ALL_RET_RANK_IN_LVL2], 0.25)
VAR Q2 = PERCENTILEX.INC(ALL('Ranks Normalised'), [BDM_ALL_RET_RANK_IN_LVL2], 0.50)
VAR Q3 = PERCENTILEX.INC(ALL('Ranks Normalised'), [BDM_ALL_RET_RANK_IN_LVL2], 0.75)
RETURN
SWITCH(
TRUE(),
score <= Q1, "#FAC9CD",
score <= Q2, "#F7F5B6",
score <= Q3, "#E6FAD6",
"#DBEDFD"
)
- wardy912
  Super User
  9 months ago
  Hi Anonymous
  
  Calculate the maximum score for the Area and then apply the quartile logic based on that maximum instead of the individual house score
  
  Quartile Conditional Format Colour Area = VAR AreaMaxScore = CALCULATE( MAX('Ranks Normalised'[BDM_ALL_RET_RANK_IN_LVL2]), ALLEXCEPT('Ranks Normalised', 'Ranks Normalised'[Area]) ) VAR Q1 = PERCENTILEX.INC(ALL('Ranks Normalised'), [BDM_ALL_RET_RANK_IN_LVL2], 0.25) VAR Q2 = PERCENTILEX.INC(ALL('Ranks Normalised'), [BDM_ALL_RET_RANK_IN_LVL2], 0.50) VAR Q3 = PERCENTILEX.INC(ALL('Ranks Normalised'), [BDM_ALL_RET_RANK_IN_LVL2], 0.75) RETURN SWITCH( TRUE(), AreaMaxScore <= Q1, "#FAC9CD", // Red AreaMaxScore <= Q2, "#F7F5B6", // Yellow AreaMaxScore <= Q3, "#E6FAD6", // Green "#DBEDFD" // Blue )
  
  --------------------------------
  I hope this helps, please give kudos and mark as solved if it does!
  
  Connect with me on LinkedIn.
  Subscribe to my YouTube channel for Fabric/Power Platform related content!
  - Anonymous
    Not applicable
    9 months ago
    Genius, Thank-you so much, I was looking at MAX but wasn't sure how to include it in the original measure.
    Seems so simple now

Forum Discussion

Conditional Formatting based on Quartile % against score

7 Replies

Related content

Conditional Formatting using DAX

Conditional Formatting

Conditional Formatting

Matrix Row Conditional Formatting

Condition formatting