Measure for colour coding

[WJ876400]

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

[v-diye-msft]

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.

[WJ876400]

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

[v-diye-msft]

Attached the pbix again.

[v-diye-msft · Accepted 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

[WJ876400]

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