Forum Discussion
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:
The end goal to look something like this:
Any help would be greatly appreciated
Nelly
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
3 Replies
- v-diye-msft
Community Support
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
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.
