Calculated difference between Assessment Scores

[pbrainard]

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]

Thanks Everyone!!

[ribisht17]

You are welcome!!

[ribisht17 · Accepted solution]

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

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 

[speedramps]

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]

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

[speedramps]

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 😀