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
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 |
Solved! Go to Solution.
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
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
Hi @v-diye-msft
thanks for your help with this. How did you calculate the TotalMarksTowards column. I have the below so far but cant seem to add up the Mark towards for each student
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |