Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
pbrainard
Helper III
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.

 

pbrainard_0-1652553279677.png

 

 

1 ACCEPTED SOLUTION
ribisht17
Super User
Super User

@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")
 
ribisht17_2-1652555391632.png

 

 
 
 
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
ribisht17_0-1652555095109.png

 

 

 

FINAL OUTPUT

 

ribisht17_3-1652555496719.png

 

 

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 

View solution in original post

6 REPLIES 6
pbrainard
Helper III
Helper III

Thanks Everyone!!

You are welcome!!

ribisht17
Super User
Super User

@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")
 
ribisht17_2-1652555391632.png

 

 
 
 
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
ribisht17_0-1652555095109.png

 

 

 

FINAL OUTPUT

 

ribisht17_3-1652555496719.png

 

 

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 

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

 

 

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors