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
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You are welcome. If my reply helped, please mark it as Answer.
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.
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
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
Proud to be a Super User!
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |