Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |