Calculated Column based on two column in the Same table

[BILearner]

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! 

[mattbrice · Accepted 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.