How do i find a maximum value based on 2 columns?

[WilliamKaro]

Hi There

I am trying to figure out how to pull out a calculation called 'based units' which would be the MAX of units non promo but it needs to be by EAN_item and MrktID and NOT period_key as i am trying to evaluate a consistant units sold week on week that wasn't on promotion. It has to stay consistant through every period. Below is an example of the data and wondered if anyone knows what query to use? I've tried CALCULATE MAX across ALL Periods but this wouldn't put product and mrkt into context. Any ideas on how i can do this? 

The end goal would to get to something like this (and if total units sold is less than base it would equal total units) with incremental units being (total units - incremental units) :

Many Thanks! 🙂

[Anonymous · Accepted solution]

Hi @WilliamKaro ,

Based on the information you have provided, I can only reproduce part of your requirements, you can provide non-private details or pbix files to achieve your final goal. Depending on the first part of the information you provide, you can follow these steps:

1.Add a measure

Measure =

CALCULATE (

    MAX ( 'Table'[Units Non_Promo] ),

    FILTER (

        ALL ( 'Table' ),

        'Table'[EAN_Item] = SELECTEDVALUE ( 'Table'[EAN_Item] )

    )

)

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}