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 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

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

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    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.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

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