cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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

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
Community Champion
Community Champion

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)
Anonymous
Not applicable

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.

Anonymous
Not applicable

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

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
Not applicable

Hi @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.

Specializing in Power Query Formula Language (M)

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)
Anonymous
Not applicable

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

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)

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

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

Anonymous
Not applicable

Great solution for beginner user of DAX , thanks!

@v-huizhn-msft

Hatsoff. It's working .superb

 

Thanks

 

 @kulchandra,

 

Your example worked except when "group" is added to a slicer. When Radimetrics is selected, the max is showing 67 instead of 12. I'm new to dax. Any suggestion on how to fix this? I have been searching the web for few days but still to no avail.... Any helps is very appreciated!

 

Thanks,

TA

jblack
Frequent Visitor

@Anonymous

 

I used this solution for a similar issue I was having. Everything works great until I want to use "Salesperson" as a slicer. Is there a way to insert a slicer from the original table that will filter out which are used in the DAX formula?

 @kulchandra,

 

Your example worked except when "group" is added to a slicer. When Radimetrics is selected, the max is showing 67 instead of 12. I'm new to dax. Any suggestion on how to fix this? I have been searching the web for few days but still to no avail.... Any helps is very appreciated!

 

Thanks,

TA

Anonymous
Not applicable

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

Hi @Anonymous,

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

Best Regards,
Angelia

Anonymous
Not applicable

Thank you very much for all your help.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.