cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbrainard
Helper III
Helper III

Calculated difference between Assessment Scores

Hello,

 

I need a way to calculate the difference between scores for Entry Assessments and Exit Assessments, per participant. What I want to see is if a participant's Exit score is higher than their Entry score. Some only have an Entry score, so I don't want to include those.

 

pbrainard_0-1652553279677.png

 

 

1 ACCEPTED SOLUTION
ribisht17
Super User
Super User

@pbrainard 

 

Step 1

Entry = CALCULATE(sum(Entry[Score]),FILTER(all(Entry), (Entry[Stage])="Entry"&& Entry[Participant]=max(Entry[Participant])))
 
Step 2
Exit = CALCULATE(sum(Entry[Score]),FILTER(all(Entry), (Entry[Stage])="Exit"&& Entry[Participant]=max(Entry[Participant])))
 
Step3
Check Exit Greater = IF(Entry[Exit] > Entry[Entry],"Greater","Not Greater")
 
ribisht17_2-1652555391632.png

 

 
 
 
Step 4//Need to get rid of the last row since there is no EXIT 
Count P = COUNTROWS(FILTER(ALL(Entry),Entry[Participant]=MAX(Entry[Participant])))
////Filter above Count P as greater than 1
ribisht17_0-1652555095109.png

 

 

 

FINAL OUTPUT

 

ribisht17_3-1652555496719.png

 

 

Regards,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

View solution in original post

6 REPLIES 6
pbrainard
Helper III
Helper III

Thanks Everyone!!

You are welcome!!

ribisht17
Super User
Super User

@pbrainard 

 

Step 1

Entry = CALCULATE(sum(Entry[Score]),FILTER(all(Entry), (Entry[Stage])="Entry"&& Entry[Participant]=max(Entry[Participant])))
 
Step 2
Exit = CALCULATE(sum(Entry[Score]),FILTER(all(Entry), (Entry[Stage])="Exit"&& Entry[Participant]=max(Entry[Participant])))
 
Step3
Check Exit Greater = IF(Entry[Exit] > Entry[Entry],"Greater","Not Greater")
 
ribisht17_2-1652555391632.png

 

 
 
 
Step 4//Need to get rid of the last row since there is no EXIT 
Count P = COUNTROWS(FILTER(ALL(Entry),Entry[Participant]=MAX(Entry[Participant])))
////Filter above Count P as greater than 1
ribisht17_0-1652555095109.png

 

 

 

FINAL OUTPUT

 

ribisht17_3-1652555496719.png

 

 

Regards,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

Really well presented and timely solution ribisht17 thank you😀

Just one suggestion ..... perhaps consider using ALLEXCEPT instead of FILTERS and MAX for performance ?

Both these methods are viable ....

Entry1 = CALCULATE(sum(Entry[Score]),FILTER(all(Entry), (Entry[Stage])="Entry"&& Entry[Participant]=max(Entry[Participant])))

Entry2 = CALCULATE(
SUM(Yourtable[Score]),
ALLEXCEPT(Yourtable,Yourtable[Participant]),
Yourtable[Stage] = "Entry")
speedramps
Super User
Super User

Consider this solution which converts Entry and Exit into separte score columns and then sums them

 

Edit the table in Power Query.

Click on the Stage column / Transform / Pivot column.

Then the create measure

 

EntryHigher =
// return 1 if Entry > Exit score, otherwise return blanks
IF ( SUM(Yourtable[Entry]) > SUM(Yourtable[Exit]),1,BLANK())
 
Create table visual with
Participant, Enty, Exit and ExitHigher
 
If you need a card total then you will need to use SUMX
ExitsHigher =
SUMX(Yourtable,[ExitHigher])
 
 

 

 

Another solution is this ....

 

EntryIsHigher =

VAR ParticipantEntry =
CALCULATE(
SUM(Yourtable[Score]),
ALLEXCEPT(Yourtable,Yourtable[Participant]),
Yourtable[Stage] = "Entry")

VAR ParticipantExit =
CALCULATE(
SUM(Yourtable[Score]),
ALLEXCEPT(Yourtable,Yourtable[Participant]),
Yourtable[Stage] = "Exit")

RETURN
// return 1 if Entry > Exit score, otherwise return blanks
IF (ParticipantEntry > ParticipantExit,1,BLANK())
 
 
If you need a card total then you will need to use SUMX
EntryIsHighers =
SUMX(Yourtable,[EntryIsHigher])
 
Please click thumbs up and accept as solution buttons. Thanks 😀

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.