calculating lower price in lists per unique product

[Anonymous]

Hi, I am trying to create a calculated column (Cheapest Key) where i want a "1" on the row if Column "Comp Segment" = Key and then i want the "1" on the row with the lowest "Pricerank". So in the picture below i only want a "1" on the row i have red circle. https://imgur.com/GuuxARh This logic should be applied on all unique products ("Sku"). Can anyone help me?

[Anonymous]

Thanks, that works! For some reason it says authentication failed when I try to accept is as solution, but as soon as it is working again Ill press accept. Thanks again many many times!

[v-cherch-msft]

Hi @Anonymous

Glad to hear you've solved it, please accept the helpful reply as solution, that way, other community members will easily find the solution when they get same issue

Regards,

Cherie

[AlB · Accepted solution]

Hi @Anonymous

Try this for the new calculated column:

Cheapest Key =
VAR _MaxSKUPrice =
    CALCULATE (
        MAX ( Table1[PriceRank] ),
        ALLEXCEPT ( Table1, Table1[SKU] ),
        Table1[Comp Segment] = "Key"
    )
RETURN
    IF ( Table1[Comp Segment] = "Key" && Table1[PriceRank] = _MaxSKUPrice, 1 )