Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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]))
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
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
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.
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.
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.
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.
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.
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
Hi
I have used the same codes and they are giving the desired result but when I deploy this report on D365 this table is not refreshed. Your early response is appreciated.
Hi @v-huizhn-msft,
Sorry for bringing this topic out from the crack.
How would it look like if antoher column would be added that always display the sales person associated to the max per group?
In this example, add a column with following values:
-Radimetrics ==>Ryan Reeve associated to each lines of this article
-Others ==> John Pace
-Medrad ==> Sean McMahan
Thanks a lot!
Great solution for beginner user of DAX , thanks!
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
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?
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
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
Thank you very much for all your help.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |