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
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi MarcelBeug, I need the solution in PowerBI and I am not familer with Power Query. Thank you for your help.

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      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.

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        Just to illustrate: in this 2-minute-video I move the data and the query from Excel to Power BI, followed by a step-by-step recap of the query.

         

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Marcel,

     

    This solution is incredible. I used it on another project.

     

    However, I just don't understand the logic of this line of code...

    = Table.AddColumn(Grouped, "Custom", (x) => Table.SelectRows(x[AllData], each [Sales] = x[MaxSales]))

     

    Why do you need to create a function? 

     

    Thanks a lot.

     

    Jason

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Marcel,

     

    This solution is amazing. I used it on another project.

     

    However, I don't understand this line of code:

    = Table.AddColumn(Grouped, "Custom", (x) => Table.SelectRows(x[AllData], each [Sales] = x[MaxSales]))

     

    Why do you need to create a function and what does it do ?


    Thanks,

     

    Jason.