cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nellygamil Frequent Visitor
Frequent Visitor

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 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:

Capture.PNG

The end goal to look something like this:

Capture2.PNG

 

Any help would be greatly appreciated

 

Nelly

1 ACCEPTED SOLUTION

Accepted Solutions
v-diye-msft Member
Member

Re: Conditional Formatting based on percentage of multiple values

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))))

1.PNG

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"))))

2.png

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

3.PNG

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

4.png

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

6.png

Best regards,

Dina Ye

2 REPLIES 2
v-diye-msft Member
Member

Re: Conditional Formatting based on percentage of multiple values

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))))

1.PNG

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"))))

2.png

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

3.PNG

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

4.png

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

6.png

Best regards,

Dina Ye

nellygamil Frequent Visitor
Frequent Visitor

Re: Conditional Formatting based on percentage of multiple values

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

 

much appreciated.

Nelly