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


v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
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


Eti_SCM's avatar
Eti_SCM
Regular Visitor
4 years ago

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!