Lookup to return only max value

[Anonymous]

Hi Superheroes!
I am having an issue with my Lookup-function returning a table instead of a single value.

I have two relevant tables, plus date.

1. Account Overview where I keep Customer ID (Number), Product ID (SKU), Year (Year), and price (Price per Unit).
2. Forecast containing Customer ID (Number), Product ID (SKU), Date (Forecast Shipping Date), and number of units forecasted (Units) which contains plus Units.

The Lookup function below is meant to return the price for a product given to a customer for the year in question, and multiply with the number if units.

However, there appears to be duplicates in the Account Overview (excel)table, i.e. multiple prices for the same product for the same customer withing the same year, and I would like to bypass this by having the below formula only return the highest price found. In short, if the formula returns more than one price for the same product to the same customer within the same year, I would only want the highest one.

Revenue =
SUMX(f_Forecast, f_Forecast[Units] *

    LOOKUPVALUE('dAccount Overview'[Price per unit],

'dAccount Overview'[SKU],      f_Forecast[SKU],

'dAccount Overview'[Number],   f_Forecast[Number],

         'dAccount Overview'[Year],  YEAR(f_Forecast[Forecast Shipping Date])

         )

    )

Any help would be much appreciated!
Daniel

[AlB · Accepted solution]

Hi @Anonymous 

Revenue =
SUMX (
    f_Forecast,
    f_Forecast[Units]
        * CALCULATE (
            MAX ( 'dAccount Overview'[Price per unit] ),
            FILTER (
                ALL (
                    'dAccount Overview'[SKU],
                    'dAccount Overview'[Number],
                    'dAccount Overview'[Year]
                ),
                'dAccount Overview'[SKU] = f_Forecast[SKU]
                    && 'dAccount Overview'[Number] = f_Forecast[Number]
                    && 'dAccount Overview'[Year] = YEAR ( f_Forecast[Forecast Shipping Date] )
            )
        )
)

 Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers