Getting highest increase in test score

[harshadrokade]

Hi All,

I have velow data. I want to get the name of that student who have shown hightest increase in his 4th test as compared to 1st test. I also want to know the % of increase achieved by that student by comparing 1st test score & 4th test score. 

Thanks in advance.

Student name	Test	Score
A	1	50
A	2	40
A	3	90
A	4	70
B	1	30
B	2	20
B	3	50
B	4	95
C	1	30
C	2	40
C	3	50
C	4	60
D	1	80
D	2	40
D	3	70
D	4	60
E	1	50
E	2	20
E	3	90
E	4	80

[parry2k · Accepted solution]

@harshadrokade add following measure to achieve it:

Sum Score = SUM ( Score[Score] )

Score Diff = 
VAR __score1st = CALCULATE ( [Sum Score], Score[Test] = 1 )
VAR __score4th = CALCULATE ( [Sum Score], Score[Test] = 4 )
VAR __diff = __score4th - __score1st
RETURN
DIVIDE ( __diff, __score1st )

Student Rank = RANKX ( ALLSELECTED ( Score[Student name] ), [Score Diff], , DESC ) 

and here it the output, tweak it as you see fit

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ALLUREAN · Accepted solution]

Hi, @harshadrokade 

Check this:

https://www.dropbox.com/s/qvygfk6v6mev9rg/Getting%20highest%20increase%20in%20test%20score%20%28allu...

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

[harshadrokade · Accepted solution]

Hi @Jihwan_Kim @ALLUREAN @parry2k 

With all of your help, I could create the measure that gives me the % of the increase/decrease in test 4 over test 1. 

When I put that measure in a table against the Student name, it gives the correct % increase/decrease however when I am trying to get the top student name out of that measure, it is giving me only top first student name correctly but when I try to find top second student name, it gives me the top first student name only.

I am using below measure to get the top 1st which is working well-

Top first student name =

MAXX (

TOPN (

1,

values ('Student name'[Student name] ),

CALCULATE ('Student name'[Compare%]),DESC

),

'Student name'[Student name])

I am getting Top first name only when I try to get the top second student name with below measure

Top first student name =

MAXX (

TOPN (

2,

values ('Student name'[Student name] ),

CALCULATE ('Student name'[Compare%]),DESC

),

'Student name'[Student name])

I can't upload the pbix file due to restrictions-

[parry2k · Accepted solution]

@harshadrokade your original question was asked and a solution was provided. The new question you asked is something totally different and the solution I provided is not suitable for it. You should accept a solution on this thread and create a new post for the 2nd question.

✨ Follow us on LinkedIn and subscribe to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[harshadrokade · Accepted solution]

Hi @Jihwan_Kim @ALLUREAN @parry2k 

With all of your help, I could create the measure that gives me the % of the increase/decrease in test 4 over test 1. 

When I put that measure in a table against the Student name, it gives the correct % increase/decrease however when I am trying to get the top student name out of that measure, it is giving me only top first student name correctly but when I try to find top second student name, it gives me the top first student name only.

I am using below measure to get the top 1st which is working well-

Top first student name =

MAXX (

TOPN (

1,

values ('Student name'[Student name] ),

CALCULATE ('Student name'[Compare%]),DESC

),

'Student name'[Student name])

I am getting Top first name only when I try to get the top second student name with below measure

Top first student name =

MAXX (

TOPN (

2,

values ('Student name'[Student name] ),

CALCULATE ('Student name'[Compare%]),DESC

),

'Student name'[Student name])

I can't upload the pbix file due to restrictions-

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

[ALLUREAN · Accepted solution]

Hi, @harshadrokade 

Check this:

https://www.dropbox.com/s/qvygfk6v6mev9rg/Getting%20highest%20increase%20in%20test%20score%20%28allu...

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

[parry2k · Accepted solution]

@harshadrokade add following measure to achieve it:

Sum Score = SUM ( Score[Score] )

Score Diff = 
VAR __score1st = CALCULATE ( [Sum Score], Score[Test] = 1 )
VAR __score4th = CALCULATE ( [Sum Score], Score[Test] = 4 )
VAR __diff = __score4th - __score1st
RETURN
DIVIDE ( __diff, __score1st )

Student Rank = RANKX ( ALLSELECTED ( Score[Student name] ), [Score Diff], , DESC ) 

and here it the output, tweak it as you see fit

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡