Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
michael_knight
Post Prodigy
Post Prodigy

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

 

Pending1.PNG

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

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
michael_knight
Post Prodigy
Post Prodigy

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Nathaniel_C
Super User
Super User

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

 

offer.PNG
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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.