Writing Formula to show MAX and MIN

[JMcAnarney1]

How can I write a formula for a new table to show the MAX and MIN for each bid event in a seperate column? For example. How can I show the Max and MIN for bid event 2506 that IS NOT the engineering estimate?

[JMcAnarney1]

So i get the #2 formula but herre is what I have for the first: 

Highest Bidder 1 =

VAR _table = CALCULATETABLE(ADDCOLUMNS(SUMMARIZE('Bid Data','Bid Data'[Bid Listing]),"Total Costs",'Bid Data'[List of Bids]),ALLSELECTED('Bid Data'[Bidder]))

VAR _max = MAXX('Bid Data',[List of Bids])

RETURN

_max

However that is not matching my List of bids numbers adjacent to it:

Is there a way I can attach my file?

[v-cgao-msft]

Hi @JMcAnarney1 ,

Please refer to the simple file:

Measure = 
VAR _year = SELECTEDVALUE('financials'[Year])
VAR _word = SELECTEDVALUE('Table'[Column1])
VAR _result = 
SWITCH(
    _word,
    "MIN",
    MINX(FILTER(SUMMARIZE('financials','financials'[Year],'financials'[Country],"S",[Sales]),'financials'[Year]=_year),[S]),
    "MAX",
    MAXX(FILTER(SUMMARIZE('financials','financials'[Year],'financials'[Country],"S",[Sales]),'financials'[Year]=_year),[S])
)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[JMcAnarney1]

I get this but i want it as it's own measure in column seperate out i.e. Min would be a column as with MAx:

So in picture case the highest bidder for 2167 was bettis so they should only appear in blank highest bidder column

[v-cgao-msft]

Hi @JMcAnarney1 ,

I updated the model.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[JMcAnarney1]

So i get the #2 formula but herre is what I have for the first: 

Highest Bidder 1 =

VAR _table = CALCULATETABLE(ADDCOLUMNS(SUMMARIZE('Bid Data','Bid Data'[Bid Listing]),"Total Costs",'Bid Data'[List of Bids]),ALLSELECTED('Bid Data'[Bidder]))

VAR _max = MAXX('Bid Data',[List of Bids])

RETURN

_max

However that is not matching my List of bids numbers adjacent to it:

Is there a way I can attach my file?

[v-cgao-msft]

Hi @JMcAnarney1 ,

You can link to a cloud file. Make sure the file doesn't contain any sensitive data before you make it public.

How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[JMcAnarney1]

Does this work??

https://topekamy.sharepoint.com/:u:/g/personal/jmcanarney_topeka_org/EYSdwW079a9Giy58rvFuNkEBlBwf-BP...

[JMcAnarney1]

https://drive.google.com/file/d/1xpPWuI8jWhqffbw9yzhyhpF-C3zPZykQ/view?usp=sharing

[v-cgao-msft · Accepted solution]

Hi @JMcAnarney1 ,

Please try:

Highest Bidder = 
VAR _table =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( 'Bid Data', 'Bid Data'[Bid Listing], 'Bid Table'[Bidder] ),
            "@Cost", [List of Bids]
        ),
        ALLSELECTED ( 'Bid Table'[Bidder] )
    )
VAR _max =
    MAXX ( _table, [@Cost] )
RETURN
    IF ( [List of Bids] && [List of Bids] = _max, _max )

Lowest Bidder = 
VAR _table =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( 'Bid Data', 'Bid Data'[Bid Listing], 'Bid Table'[Bidder] ),
            "@Cost", [List of Bids]
        ),
        ALLSELECTED ( 'Bid Table'[Bidder] )
    )
VAR _min =
    MINX ( _table, [@Cost] )
RETURN
    IF ( [List of Bids] && [List of Bids] = _min, _min )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum