Show the biggest change based on a group.

[Anonymous]

I have data that looks something like the table below the real table has 30 users and 40 different skills, I would like to create a single row card, that basically shows
"User A skill 1.2 has seen the biggest change" obviousy A will change based on the slicer i have applied to the page. but i cant work out how to use a measure that will return the biggest change in the table.

User	date	Skill	Score
A	01/01/2020	1.1	5
A	02/01/2020	1.2	5
A	03/01/2020	1.3	4
A	04/01/2020	1.1	4
A	05/01/2020	1.2	8
A	06/01/2020	1.3	6
B	01/01/2020	1.1	5
B	02/01/2020	1.2	5
B	03/01/2020	1.3	4
B	04/01/2020	1.1	4
B	05/01/2020	1.2	8
B	06/01/2020	1.3	6

[AllisonKennedy]

You may want to check out some custom visuals like this one if you are able to use marketplace visuals, https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380950?tab=Overview

To find the biggest change, just do MAX(Score) - MIN(Score), the trick is just considering what filters you want to add/keep remove.

So in your case I think you want to find largest change per Skill?

Do you also want to consider that the change has to be an improvement? So do we need to take date into account or can we just find the overall MAX() - MIN()?

[Anonymous]

Thats a really good point, i need to take date and only want to know if it was positive. 

[AllisonKennedy · Accepted solution]

@Anonymous  See if this file helps you any. 

Attached below signature

[Anonymous]

This is fantastic exactly what i was after thank you ever so much! 

[mahoneypat]

Please try this measure expression in a card visual.  Note it requires that there is a unique value for max.  If there are duplicates, a more complex expression could concatenate those Users/Skills together.

NewMeasure =
VAR maxvalue =
MAX ( User[Score] )
VAR maxuser =
CALCULATE ( MIN ( User[User] ), User[Score] = maxvalue )
VAR maxskill =
CALCULATE ( MIN ( User[Skill ] ), User[Score] = maxvalue )
RETURN
"User " & maxuser & " & Skill " & maxskill & " had the greatest change"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@Anonymous , try

calculate(lastnonblankvalue(Table[score], max(Table[Skill])))

calculate(lastnonblankvalue(Table[score], max(Table[Skill])),allselected(Table))