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


  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

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

    Best Regards,
    Angelia

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thank you very much for all your help.

  • tabbey's avatar
    tabbey
    Frequent Visitor
    8 years ago

     @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

  • tabbey's avatar
    tabbey
    Frequent Visitor
    8 years ago

     @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's avatar
    Anonymous
    Not applicable
    7 years ago

    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?

  • harib's avatar
    harib
    Post Patron
    7 years ago

    v-huizhn-msft

    Hatsoff. It's working .superb

     

    Thanks

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Great solution for beginner user of DAX , thanks!

  • 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!

  • rsingh's avatar
    rsingh
    Frequent Visitor
    4 years ago

    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.