Hi,
I have a data similar to attached screenshot. And I am trying to calculate the Weighted Score column based on Subject Name and test Score columns.
The weighted column is calculated for the range of Test score from 0 to 100 or Null from a sliding scale which varies for each subject name.
For example If the subject is Math the weighted Score Would be calculated with following Sliding Scale
If the subject is math and the test score is null then weighted score is null
If the subject is math and the test score >= 90 then weighted Score is "4.0"
if the subject is math and the test score >=80 but <90 then weighted score is "3.5"
if the subject is math and the test score >=70 but <80 then weighted score is "3.0"
if the subject is math and the test score >=60 but <70 then weighted score is "2.5"
if the subject is math and the test score >=50 but <60 then weighted score is "2.0"
if the subject is math and the test score >=40 but <50 then weighted score is "1.5"
if the subject is math and the test score >=35 but <40 then weighted score is "1.0"
if the subject is math and the test score <30 then weighted score is "0"
otherwise null
Similarly for Science it may be
If the subject is Science and the test score is null then weighted score is null
If the subject is Sceince and the test score >= 75 then weighted Score is "4.0"
if the subject is math and the test score >=70 but <75 then weighted score is "3.5"
if the subject is math and the test score >=60 but <70 then weighted score is "3.0"
if the subject is math and the test score >=55 but <60 then weighted score is "2.5"
if the subject is math and the test score >=50 but <55 then weighted score is "2.0"
if the subject is math and the test score >=40 but <50 then weighted score is "1.5"
if the subject is math and the test score >=35 but <40 then weighted score is "1.0"
if the subject is math and the test score <30 then weighted score is "0"
otherwise null
In the original data I have more than 30 Subjects for each row irrespective of the student is taking that subject or not if the student is not taking the subject the corresponding Scores are blank/null.
Can somebody please point me in the right direction on how to acheive this Preferably using DAX if not Power Query will also do.
Thanks!
Solved! Go to Solution.
As a calculated column you could do this:
=SWITCH( TRUE(), Table[Subject Name] = "Math" && isblank( Table[Test Score] ), BLANK(), Table[Subject Name] = "Math" && Table[Test Score] >= 90, "4.0", Table[Subject Name] = "Math" && Table[Test Score] >=80 && Table[Test Score] <90,"3.5", Table[Subject Name] = "Math" && Table[Test Score] >=70 && Table[Test Score] <80, "3.0", . . Table[Subject Name] = "Science" && isblank( Table[Test Score] ), BLANK(), Table[Subject Name] = "Science" && Table[Test Score] >= 75, "4.0", Table[Subject Name] = "Science" && Table[Test Score] >= 70 && Table[Test Score] < 75,"3.5", . . )
Obvioulsy I didn't fill out the whole thing, but you get the idea. If you have a duplicative weighted schedule, you could "OR" the Subject Name checks in the SWITCH so as to not have to do a row for every subject. Keep in mind the SWITCH will stop at the first TRUE statement.
Another approach would be to have nested SWITCH statements, Outer switches on subject name, inner by score range.
As a calculated column you could do this:
=SWITCH( TRUE(), Table[Subject Name] = "Math" && isblank( Table[Test Score] ), BLANK(), Table[Subject Name] = "Math" && Table[Test Score] >= 90, "4.0", Table[Subject Name] = "Math" && Table[Test Score] >=80 && Table[Test Score] <90,"3.5", Table[Subject Name] = "Math" && Table[Test Score] >=70 && Table[Test Score] <80, "3.0", . . Table[Subject Name] = "Science" && isblank( Table[Test Score] ), BLANK(), Table[Subject Name] = "Science" && Table[Test Score] >= 75, "4.0", Table[Subject Name] = "Science" && Table[Test Score] >= 70 && Table[Test Score] < 75,"3.5", . . )
Obvioulsy I didn't fill out the whole thing, but you get the idea. If you have a duplicative weighted schedule, you could "OR" the Subject Name checks in the SWITCH so as to not have to do a row for every subject. Keep in mind the SWITCH will stop at the first TRUE statement.
Another approach would be to have nested SWITCH statements, Outer switches on subject name, inner by score range.
Each week, complete activities and be qualified in the drawing for cool Power BI Swag.
User | Count |
---|---|
100 | |
57 | |
55 | |
54 | |
49 |