cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BILearner Frequent Visitor
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.

Capture.PNG

 

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
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.  

 

 

1 REPLY 1
mattbrice Senior Member
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.  

 

 

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 179 members 1,784 guests
Please welcome our newest community members: