cancel
Showing results for
Did you mean:
BILearner Frequent Visitor

Calculated Column based on two column in the Same table

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!

1 ACCEPTED SOLUTION

Accepted Solutions
mattbrice Senior Member

Re: Calculated Column based on two column in the Same table

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.

mattbrice Senior Member

Re: Calculated Column based on two column in the Same table

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.

Announcements Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Top Kudoed Authors
Users Online
Currently online: 179 members 1,784 guests
Recent signins:
• GauravSingh • smpa01 • aqavi10 • gmaciel • hdzhendov • sfog • Bikea01 • KyleFoster • powerbipresto • SysvisionPBI • asellars1 • jbJB • Hydr0x • shahrukh_naeem 