Forum Discussion
Filter only highest value by category
Hi All,
I am really stuck in filtering/sort Sales Person by highest Sales in each Group. Please see the below table. or click on this link to acces PowerBi Table: https://app.powerbi.com/groups/me/dashboards/b3b657db-34e8-41bc-a7fd-6a8902561186
I have created table in PowerBi and my data may refreshed or changed time to time. I need dynamic solution which will also work when data is refreshed or changed. If you need my PowerBI file please let me know.
Could you please help to find out this? Thank you in advance.
| Group | Sales Person | Sales |
| Radimetrics™ | Christine Shaw | 5 |
| Radimetrics™ | Jeffrey Evans | 2 |
| Radimetrics™ | Jennifer Stewart | 2 |
| Radimetrics™ | Monique Rowley | 3 |
| Radimetrics™ | Rachael Viner | 3 |
| Radimetrics™ | Ryan Reeve | 12 |
| Radimetrics™ | Sean McMahan | 3 |
| Radimetrics™ | Tara Larrea | 4 |
| Radimetrics™ | Timothy Grobe | 1 |
| Others | Jennifer Stewart | 9 |
| Others | John Collado | 7 |
| Others | John Ferriter | 1 |
| Others | John Pace | 67 |
| Others | Julie Springer | 11 |
| Others | Karen Broyles | 4 |
| Others | Keith Patterson | 1 |
| Others | Ryan Reeve | 10 |
| Others | Sean Casey | 5 |
| Medrad® | Ryan Reeve | 1 |
| Medrad® | Sean McMahan | 8 |
| Medrad® | Tara Larrea | 1 |
| Medrad® | Vanessa Deleon | 2 |
| Gadavist | Cheryl Mccleary-Bowser | 2 |
| Gadavist | Cynthia Powers | 1 |
| Gadavist | Doug Blaheta | 2 |
| Gadavist | Lianne Pompeo | 5 |
| Gadavist | Maritzia Zilles | 4 |
| Gadavist | Robert Kilkelly | 1 |
Hi Anonymous,
You'd better create calculated columns to get the max sale for each group, then create a new table to display what you want. I try to reproduce your scenario and get expected result as follows.First, create a calculated column using the following formula.
Max = CALCULATE(MAX(Test[Sales]),ALLEXCEPT(Test,Test[Group]))
Then click the New table under Modeling, type the DAX and create a new table. Please the result in screenshot, the result table will refresh when your data refresh.
Expected result = SELECTCOLUMNS(FILTER(Test,Test[Sales]=Test[Max]),"Group",Test[Group],"Sales Person",Test[Sales Person],"highest",Test[Max])
If you have any other issue, please feel free to ask.
Best Regards,
Angelia
20 Replies
In Power Query (M) this can be done with the Group By option, selecting both All Rows and Max Sales (step "Grouped" below).
This will create a nested table and an additional column with the max sales.
The next step is selecting the records from the nested table with sales = max sales.
Code below created in Excel Power Query.
let Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content], Typed1 = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Sales Person", type text}, {"Sales", Int64.Type}}), Grouped = Table.Group(Typed1, {"Group"}, {{"AllData", each _, type table}, {"MaxSales", each List.Max([Sales]), type number}}), SelectedMax = Table.AddColumn(Grouped, "Custom", (x) => Table.SelectRows(x[AllData], each [Sales] = x[MaxSales])), RemovedColumns = Table.RemoveColumns(SelectedMax,{"AllData", "MaxSales"}), Expanded = Table.ExpandTableColumn(RemovedColumns, "Custom", {"Sales Person", "Sales"}, {"Sales Person", "Sales"}), Typed2 = Table.TransformColumnTypes(Expanded,{{"Sales Person", type text}, {"Sales", type number}}) in Typed2Hi MarcelBeug, I need the solution in PowerBI and I am not familer with Power Query. Thank you for your help.
This code, except for the first line, can also be used in Power BI (via Get Data which is actually Power Query).
Otherwise you may be looking for a DAX solution, which is not my specialism.
Thanks so much. Did not realize you could select all rows as well as a group by for the group by transformation. This is incredible and is similar to being able to group by with a having clause in SQL.
