visual showing max value for each year with corresponding name

[kkassel]

I have a set of data where I'm trying to create a table or matrix that shows the max value of a given year and the corresponding name of the person.  This data is NBA stats. I want to be able to show the player in each season with the highest points per game. With the table sorted in season order. Below I have a small sample set and then what I want the visual to display.

Sample Data:

Year	Player	Games Played	Points	Pts per G
2016	James Harden	82	2376	29.0
2017	Russell Westbrook	81	2558	31.6
2017	James Harden	81	2356	29.1
2015	James Harden	81	2217	27.4
2016	Stephen Curry	79	2375	30.1
2017	Isaiah Thomas	76	2199	28.9
2017	Anthony Davis	75	2099	28.0
2016	Kevin Durant	72	2029	28.2
2015	LeBron James	69	1743	25.3
2015	Anthony Davis	68	1656	24.4
2015	Russell Westbrook	67	1886	28.1
2015	Kevin Durant	27	686	25.4

Visual Result:

Year	Player	Pts per G
2015	Russell Westbrook	28.1
2016	Stephen Curry	30.1
2017	Russell Westbrook	31.6

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

PPG = MAX(Data[Pts per G])

Pl = CONCATENATEX(TOPN(1,VALUES(Data[Player]),[PPG]),Data[Player],", ")

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHLasMwEEV/RXgdjDWyHl62zaKvVVvoImShEoFNE6lIdiB/X80oIY4hG4lBZ+7cudpsKmi4qlbVqz24xJ5t3DmfSwP5AKHxCbpquyJQ5+pjSsnt9+zbpfEnhvCLNEdMSpMvwWs155fChApZhGt+QeU9FEgFdN1eUGz9HN1f7zx7mmI85Vp3xS/KiOYqi70vyQ62Z199ONiELM3mHbWY+ma7Bz/2wZ/Y2h4HYlEQmjM7d/DmjoNn6ylaPyJIgTVwFoX5Xu/uMQbPaL1cKmS4bgWFVos5upyvMFKuSlztNQN55ycULsGNUcXGTbwLx6BJXxUXWXn7Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Player = _t, #"Games Played" = _t, Points = _t, #"Pts per G" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Player", type text}, {"Games Played", Int64.Type}, {"Points", Int64.Type}, {"Pts per G", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year"}, {{"Pl", each Table.Max(_,"Player")}, {"Max", each List.Max([Pts per G]), type nullable number}}),
    #"Expanded Pl" = Table.ExpandRecordColumn(#"Grouped Rows", "Pl", {"Player"}, {"Player"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Pl",{{"Year", Order.Ascending}})
in
    #"Sorted Rows"

Hope this helps.

[ThxAlot · Accepted solution]

[v-xuxinyi-msft · Accepted solution]

Hi @kkassel 

@ThxAlot @Ashish_Mathur  GOOD ANSWER!

I have another method.

You can create a measure.

Measure = 
VAR _max = CALCULATE(MAX([Pts per G]), ALLEXCEPT('Table', 'Table'[Year]))
RETURN
IF( MAX([Pts per G]) = _max, 1, 0) 

Put the measure into the visual-level filters, set up show items when the value is 1.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ThxAlot · Accepted solution]

[Ashish_Mathur · Accepted solution]

Hi,

This M code works as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHLasMwEEV/RXgdjDWyHl62zaKvVVvoImShEoFNE6lIdiB/X80oIY4hG4lBZ+7cudpsKmi4qlbVqz24xJ5t3DmfSwP5AKHxCbpquyJQ5+pjSsnt9+zbpfEnhvCLNEdMSpMvwWs155fChApZhGt+QeU9FEgFdN1eUGz9HN1f7zx7mmI85Vp3xS/KiOYqi70vyQ62Z199ONiELM3mHbWY+ma7Bz/2wZ/Y2h4HYlEQmjM7d/DmjoNn6ylaPyJIgTVwFoX5Xu/uMQbPaL1cKmS4bgWFVos5upyvMFKuSlztNQN55ycULsGNUcXGTbwLx6BJXxUXWXn7Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Player = _t, #"Games Played" = _t, Points = _t, #"Pts per G" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Player", type text}, {"Games Played", Int64.Type}, {"Points", Int64.Type}, {"Pts per G", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year"}, {{"Pl", each Table.Max(_,"Player")}, {"Max", each List.Max([Pts per G]), type nullable number}}),
    #"Expanded Pl" = Table.ExpandRecordColumn(#"Grouped Rows", "Pl", {"Player"}, {"Player"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Pl",{{"Year", Order.Ascending}})
in
    #"Sorted Rows"

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

PPG = MAX(Data[Pts per G])

Pl = CONCATENATEX(TOPN(1,VALUES(Data[Player]),[PPG]),Data[Player],", ")

Hope this helps.