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

1 ACCEPTED SOLUTION
Super User

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")

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

FINAL OUTPUT

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
6 REPLIES 6
Helper III

Thanks Everyone!!

Super User

You are welcome!!

Super User

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")

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

FINAL OUTPUT

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
Super User
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")
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])

Super User

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

Check it Out!

Click here to read more about the November 2022 updates!

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

Top Solution Authors
Top Kudoed Authors