Forum Discussion

michael_knight's avatar
michael_knight
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Return largest value

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 Stat...
Need Help
Tips and Tricks
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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