Conditional Formatting based on percentage of multiple values

[nellygamil]

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 easily done in excel but I cannot figure it out in Power BI.

In my dataset, I have a column "Marks", where students can only be awarded a grade from 1 to 4

I want to visualize in a matrix if they have received specific percentages as below:

The end goal to look something like this:

Any help would be greatly appreciated

Nelly

[v-diye-msft · Accepted solution]

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-jgbZq...

Best regards,

Dina Ye

[nellygamil]

@v-diye-msft  Thank you so much for this, worked perfectly 🙂

much appreciated.

Nelly

[Anonymous]

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.