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

You're welcome.

 

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

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