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

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.

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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. :)

  • 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


      • 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