Forum Discussion

nellygamil's avatar
nellygamil
Frequent Visitor
7 years ago
Solved

Conditional Formatting based on percentage of multiple values

Hi All,   For the education sector, I am trying to develop a conditional format that either adds a word or conditional format a value based on the overall grades for students as below, it can be ea...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    Hi nellygamil ,

     

    I’ve created a table like this :

    Forename

    Curriculum

    Score

    a

    O

    1

    a

    N

    2

    a

    M

    4

    b

    O

    2

    b

    M

    3

    b

    N

    1

    c

    O

    1

    c

    M

    1

    c

    N

    2

    And created a measure to generate the result as your requested:

    Measure = var a = IF(CALCULATE(DISTINCTCOUNT(Table1[Score]),Table1[Score]=4)/DISTINCTCOUNT(Table1[Score])>1/10,"Red",BLANK())
var b = IF(CALCULATE(DISTINCTCOUNT(Table1[Score]),Table1[Score]=3)/DISTINCTCOUNT(Table1[Score])>1/5&&a=BLANK(),"Amber",BLANK())
var c = CALCULATE(DISTINCTCOUNT(Table1[Score]),Table1[Score]=3/DISTINCTCOUNT(Table1[Score]))
var d = IF(b=BLANK()&&c<1/5&&c>1/20,"Green",BLANK())
var e = IF(d=BLANK()||CALCULATE(DISTINCTCOUNT(Table1[Score]),Table1[Score]=1)/DISTINCTCOUNT(Table1[Score])>2/5,"Gold")
Return
IF(a<>BLANK(),a,IF(b<>BLANK(),b,IF(d<>BLANK(),d,IF(e<>BLANK(),e))))

    Then we got the text “Red”,”Amber”,etc.

    I added another measure as the conditional format using the code:

    Measure 2 = IF([Measure]="Red","#FF0A18",IF([Measure]="Amber","#FF8500",IF([Measure]="Gold","#FFF863",IF([Measure]="Green","#38FF3A","#000000"))))

    You can find the color code under custom color in format pane

    Then apply the measure 2 as format by clicking the conditional formatting>Background color>Field value

    Until we get it. Attached pbix here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EUBO626SMOpDoUWe-jgbZqkBwQ5rdW34cpPmGyznhBWvSQ?e=eYlUEH

    Best regards,

    Dina Ye

nellygamil's avatar
nellygamil
Frequent Visitor
7 years ago

v-diye-msft  Thank you so much for this, worked perfectly :)

 

much appreciated.

Nelly

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hello v-diye-msft  , i'm having a similar problem (which i posted on another topic).

From my understanding you are calculating a MeasureColor from another Measure, and then you attribute the MeasureColor to the background of the Measure.

My problem is that i'm trying to do exactly the same but "OK" button get's grey - i can't select a measure to define the colour of another measure.