cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kulchandra Member
Member

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.

 

GroupSales PersonSales
Radimetrics™Christine Shaw5
Radimetrics™Jeffrey Evans2
Radimetrics™Jennifer Stewart2
Radimetrics™Monique Rowley3
Radimetrics™Rachael Viner3
Radimetrics™Ryan Reeve12
Radimetrics™Sean McMahan3
Radimetrics™Tara Larrea4
Radimetrics™Timothy Grobe1
OthersJennifer Stewart9
OthersJohn Collado7
OthersJohn Ferriter1
OthersJohn Pace67
OthersJulie Springer11
OthersKaren Broyles4
OthersKeith Patterson1
OthersRyan Reeve10
OthersSean Casey5
Medrad®Ryan Reeve1
Medrad®Sean McMahan8
Medrad®Tara Larrea1
Medrad®Vanessa Deleon2
GadavistCheryl Mccleary-Bowser2
GadavistCynthia Powers1
GadavistDoug Blaheta2
GadavistLianne Pompeo5
GadavistMaritzia Zilles4
GadavistRobert Kilkelly1

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft v-huizhn-msft
Microsoft

Re: Filter only highest value by category

Hi @kulchandra,

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]))

1.PNG

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])


1.PNG

If you have any other issue, please feel free to ask.

Best Regards,
Angelia


View solution in original post

18 REPLIES 18
MarcelBeug Super Contributor
Super Contributor

Re: Filter only highest value by category

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
Specializing in Power Query Formula Language (M)
kulchandra Member
Member

Re: Filter only highest value by category

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

MarcelBeug Super Contributor
Super Contributor

Re: Filter only highest value by category

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.

Specializing in Power Query Formula Language (M)
MarcelBeug Super Contributor
Super Contributor

Re: Filter only highest value by category

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.

 

Specializing in Power Query Formula Language (M)
kulchandra Member
Member

Re: Filter only highest value by category

Hi @MarcelBeug, Thank you for your solution. It worked, but there i think it is stactic and some limitation too. 

What if my data is changed/refreshed. Is there any dynamic solution which will work even when data is refrshed.

 

I think I am looking for some DAX in powerBI, not sure yet. I already have data in PowerBi tables. if you want to look at file please let me know.

 

You are great!! You worked on my problem and created a short video too. Many thanks to you. 🙂

MarcelBeug Super Contributor
Super Contributor

Re: Filter only highest value by category

You're welcome.

 

The solution will be dynamic if you adjust the source to the actual source of your data.

Specializing in Power Query Formula Language (M)
Highlighted
Microsoft v-huizhn-msft
Microsoft

Re: Filter only highest value by category

Hi @kulchandra,

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]))

1.PNG

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])


1.PNG

If you have any other issue, please feel free to ask.

Best Regards,
Angelia


View solution in original post

kulchandra Member
Member

Re: Filter only highest value by category

Thank you very much. You saved my life. Will this second table will updated when my original table value changed. Is this dynamic solution?

 

Best, Kulchandra

Microsoft v-huizhn-msft
Microsoft

Re: Filter only highest value by category

Hi @kulchandra,

As I tested, when the resource data changes, the result table will update when I click the refresh.

Best Regards,
Angelia

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)