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 their Entry score. Some only have an Entry score, so I don't want to include those.

 

 

 

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 

6 Replies

  • 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")
  • 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 ğŸ˜€