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 
ribisht17's avatar
ribisht17
Super User
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
    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")