Forum Discussion
visual showing max value for each year with corresponding name
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 |
Hi,
Try these measures
PPG = MAX(Data[Pts per G])Pl = CONCATENATEX(TOPN(1,VALUES(Data[Player]),[PPG]),Data[Player],", ")Hope this helps.
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.
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- Ashish_Mathur
Super User
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
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
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.