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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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