Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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/d...
Need Help
Show and Tell
Tutorial Requests
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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


MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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
    Typed2
jwademcg's avatar
jwademcg
Advocate I
9 years ago

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.