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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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