Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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.
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.
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,
Try these measures
PPG = MAX(Data[Pts per G])
Pl = CONCATENATEX(TOPN(1,VALUES(Data[Player]),[PPG]),Data[Player],", ")
Hope this helps.
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |