Return largest value

[michael_knight]

Hi,

I'm trying to create a column that will display the largest value offer of a single property

This is what it would look like on Excel. It displays the largest Total IN with the Offer Status of Pending. The Theory is pretty straight forward but I'm having a hard time incorportating it into Power BI

Any suggestions will be welcome. I'll attach the PBIX file of the example 

https://www.dropbox.com/s/iih6b46b8q0t6u3/Pending%20Offers.pbix?dl=0

Cheers,

Mike

[michael_knight]

Thank you guy! @Ashish_Mathur @Nathaniel_C @mahoneypat 

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = if(AND(CALCULATE(MAX(Sheet1[Total IIN]),FILTER(Sheet1,Sheet1[Property]=EARLIER(Sheet1[Property])&&Sheet1[Offer Status]="Pending"))=Sheet1[Total IIN],Sheet1[Offer Status]="pending"),CALCULATE(MAX(Sheet1[Total IIN]),FILTER(Sheet1,Sheet1[Property]=EARLIER(Sheet1[Property])&&Sheet1[Offer Status]="Pending")),BLANK())

Hope this helps.

[Nathaniel_C]

Hi @michael_knight ,
Try this

Max Offer =
VAR CurrentProp =
    MAX ( Sheet1[Property] )
VAR _calc =
    CALCULATE (
        MAX ( Sheet1[Total IIN] ),
        FILTER (
            ALLEXCEPT ( Sheet1, Sheet1[Property] ),
            MAX ( Sheet1[Property] ) = CurrentProp
        )
    )
VAR MOffer =
    IF ( _calc = MAX ( Sheet1[Total IIN] ), MAX ( Sheet1[Total IIN] ), "" )
RETURN
    MOffer

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @michael_knight ,
Sorry, missed the Pending.  Here is the measure

Max Offer =
VAR CurrentProp =
    MAX ( Sheet1[Property] )
VAR _calc =
    CALCULATE (
        MAX ( Sheet1[Total IIN] ),
        FILTER (
            ALLEXCEPT ( Sheet1, Sheet1[Property] ),
            MAX ( Sheet1[Property] ) = CurrentProp
                && Sheet1[Offer Status] = "Pending"
        )
    )
VAR MOffer =
    IF ( _calc = MAX ( Sheet1[Total IIN] ), MAX ( Sheet1[Total IIN] ), "" )
RETURN
    MOffer

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[mahoneypat · Accepted solution]

Not sure if you wanted a column or a measure expression, but here is a column expression that gets your desired results.

Display Offer =
VAR thisoffer = Sheet1[Total IIN]
VAR maxoffer =
    CALCULATE (
        MAX ( Sheet1[Total IIN] ),
        ALLEXCEPT (
            Sheet1,
            Sheet1[Property]
        ),
        Sheet1[Offer Status] = "Pending"
    )
RETURN
    IF (
        AND (
            thisoffer = maxoffer,
            Sheet1[Offer Status] = "Pending"
        ),
        thisoffer,
        0
    )

Regards,

Pat