Forum Discussion
Measure for colour coding
Hi
I have the below table, it shows 3 students and 3 modules, each module has two elements to it. The 4th column shows what % that contributes to the grade. Below is what I have done on excel and the formulas I have used and I am looking to replicate this on BI.
What I want to do is calculate-
the weighting Factor which is =D2/240
Marks towards Total Grade =(E2*F2/SUM(F2:F7)
Total Mark =SUM(G2:G7)
RAG Status =IF(COUNTIF(E2:E7,"<40")>0,"Red",IF(AVERAGE(E2:E7)>=60,"Green","Amber"))
I am struggling with how to do the sums for each student, to count the six rows for each stucdent and get the Marks towards total grade and Total Mark and RAG status.
Hope this makes sense any help would be greatly appreciated
| A | B | C | D | E | F | G | H | I | |
| 1 | Name | Module | Type | Weighting towards 100% Module | Mark | Weighting Factor | Mark towards total Grade | Total Mark | RAG Status |
| 2 | Student 1 | 1 | Coursework | 70 | 50 | 0.291666667 | 11.66666667 | 54.78333333 | Red |
| 3 | Student 1 | 1 | Exam | 30 | 60 | 0.125 | 6 | ||
| 4 | Student 1 | 2 | Coursework | 50 | 38 | 0.208333333 | 6.333333333 | ||
| 5 | Student 1 | 2 | Exam | 50 | 28 | 0.208333333 | 4.666666667 | ||
| 6 | Student 1 | 3 | Coursework | 65 | 71 | 0.270833333 | 15.38333333 | ||
| 7 | Student 1 | 3 | Exam | 35 | 92 | 0.145833333 | 10.73333333 | ||
| 8 | Student 2 | 1 | Coursework | 70 | 70 | 0.291666667 | 16.33333333 | 79.95 | Green |
| 9 | Student 2 | 1 | Exam | 30 | 67 | 0.125 | 6.7 | ||
| 10 | Student 2 | 2 | Coursework | 50 | 89 | 0.208333333 | 14.83333333 | ||
| 11 | Student 2 | 2 | Exam | 50 | 90 | 0.208333333 | 15 | ||
| 12 | Student 2 | 3 | Coursework | 65 | 90 | 0.270833333 | 19.5 | ||
| 13 | Student 2 | 3 | Exam | 35 | 65 | 0.145833333 | 7.583333333 | ||
| 14 | Student 3 | 1 | Coursework | 70 | 45 | 0.291666667 | 10.5 | 51.03333333 | Amber |
| 15 | Student 3 | 1 | Exam | 30 | 52 | 0.125 | 5.2 | ||
| 16 | Student 3 | 2 | Coursework | 50 | 48 | 0.208333333 | 8 | ||
| 17 | Student 3 | 2 | Exam | 50 | 51 | 0.208333333 | 8.5 | ||
| 18 | Student 3 | 3 | Coursework | 65 | 60 | 0.270833333 | 13 | ||
| 19 | Student 3 | 3 | Exam | 35 | 50 | 0.145833333 | 5.833333333 |
Hi WJ876400
I just renamed the measures so you can tell them. 1 point need to confirm that Total Mark =SUM(G2:G7) you mentioned before should be named as Total Mark towards Total Grade (I use TotalMarkTowards in measure), and the real Total Marks = SUM(E2:E7)
Then total Mark Towards for each Student which adds up their marks towards total grade =:
Measure = [Marks towardsTotalGrade]+[ToTalMarktowards]
And the conditional color based on it. do you mean like this below?
You can refer to this similar post for reference: https://community.powerbi.com/t5/Desktop/Conditional-formatting-on-Strings-table-cells/td-p/784516
5 Replies
Hi WJ876400
Based on your logic, please use below measures:
Measure = CALCULATE(SUM('Table (2)'[Mark]),ALLEXCEPT('Table (2)','Table (2)'[Name]))Measure 2 = MAX('Table (2)'[Weighting towards 100% Module])*MAX('Table (2)'[Mark])/[Measure]Measure 3 = CALCULATE(SUM('Table (2)'[Weighting Factor]),ALLEXCEPT('Table (2)','Table (2)'[Name]))Measure 4 = var a = CALCULATE(COUNT('Table (2)'[Mark]),FILTER(ALLEXCEPT('Table (2)','Table (2)'[Name]),[Mark]<40)) var b = CALCULATE(AVERAGE('Table (2)'[Mark]),ALLEXCEPT('Table (2)','Table (2)'[Name])) Return IF(a>0,"Red",IF(b>=60,"Green","Amber"))PBIX attached.
Hi v-diye-msft
Thank you for sending this i will work through it and see if it works. How do I get a total Mark for each Student which adds up their marks towards total grade? And then have the colour based on that?
thanks again
Hi WJ876400
I just renamed the measures so you can tell them. 1 point need to confirm that Total Mark =SUM(G2:G7) you mentioned before should be named as Total Mark towards Total Grade (I use TotalMarkTowards in measure), and the real Total Marks = SUM(E2:E7)
Then total Mark Towards for each Student which adds up their marks towards total grade =:
Measure = [Marks towardsTotalGrade]+[ToTalMarktowards]
And the conditional color based on it. do you mean like this below?
You can refer to this similar post for reference: https://community.powerbi.com/t5/Desktop/Conditional-formatting-on-Strings-table-cells/td-p/784516
