Forum Discussion

bundipapa's avatar
bundipapa
Helper I
3 years ago
Solved

Create a max if logic per category

Hello,

 

this might be the stupidest question, but I need to recreate a maxif logic from excel showing the produt, that had the highest revenue within a deal.

Here is an example dataset in excel, that is identical in form to what I have in Power BI

In excel I would add a column showing the highest revenue for each deal

And then check if the revenue for the given row is equal to the maximum for the dea. If yes, that product has the highest and is marked, if not, I leave it empty.

 

Is there a way to recreate this in power BI with the intial dataset?  I would need the exact same result.

Thank you for any help!



 

Need Help
Tips and Tricks
Tutorial Requests
  • PhilipTreacy's avatar
    PhilipTreacy
    3 years ago

    Hi bundipapa 

     

    Download example PBIX file

     

    You can do the MAXIF like this

     

    Maxif = CALCULATE(MAX('DataTable'[Revenue]), FILTER(ALL('DataTable'), 'DataTable'[Deal] = SELECTEDVALUE('DataTable'[Deal])))

     

     

    and the Highest Product column like this

     

    Highest Product Within Deal = IF([Maxif] = SELECTEDVALUE('DataTable'[Revenue]), "Y")

     

     

     

    Regards

     

    Phil

1 Reply

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    Hi bundipapa 

     

    Download example PBIX file

     

    You can do the MAXIF like this

     

    Maxif = CALCULATE(MAX('DataTable'[Revenue]), FILTER(ALL('DataTable'), 'DataTable'[Deal] = SELECTEDVALUE('DataTable'[Deal])))

     

     

    and the Highest Product column like this

     

    Highest Product Within Deal = IF([Maxif] = SELECTEDVALUE('DataTable'[Revenue]), "Y")

     

     

     

    Regards

     

    Phil