Forum Discussion

pbrainard's avatar
pbrainard
Helper III
4 years ago
Solved

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 thei...
Need Help
  • ribisht17's avatar
    ribisht17
    4 years ago

    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's avatar
speedramps
Super User
4 years ago

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's avatar
speedramps
Super User
4 years ago

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 ðŸ˜€