Question on finding Max for Sum per category?

[JMcAnarney1]

Hello, i have following data; how can I write a formula to show the max bid per event based on total cost that is not the engineering estimate.  I do know to get the formula for the sum is the following but do not know how to get max : 

Highest Non Engineering Estimate = Calculate(SUM('Bid Template Data'[Total Cost]),'Bid Template Data'[Bidder] <> "Engineering Estimate")

How can I add a max function for this???

[johnt75 · Accepted solution]

Max Non Engineering Estimate =
MAXX (
    SUMMARIZE (
        'Table',
        'Table'[Bid event id],
        'Table'[Bidder],
        'Table'[Description]
    ),
    [Highest Non Engineering Estimate]
)

You need to put in the SUMMARIZE enough columns to uniquely identify a row. If there is a unique ID in your dataset then you could just use that column.

[tamerj1]

Hi @JMcAnarney1 

please try

Maximum None Engineering Bid =
SUMX (
SUMMARIZE (
FILTER ( 'Table', 'Table'[Bidder] <> "Engineering Estimate" ),
'Table'[Bid event id],
'Table'[Description],
"MaxBid", MAX ( 'Bid Template Data'[Total Cost] )
),
[MaxBid]
)

[johnt75]

You can use

Max Non Engineering Estimate =
MAXX ( VALUES ( 'Table'[Bid event id] ), [Highest Non Engineering Estimate] )

[JMcAnarney1]

Issue is that does not divide out the multiple non - engeering estimate bidders and it sums them together.  For example, there were two other bidders and it is lumping them together based off your formula when in reality just want one that is highest

[johnt75 · Accepted solution]

Max Non Engineering Estimate =
MAXX (
    SUMMARIZE (
        'Table',
        'Table'[Bid event id],
        'Table'[Bidder],
        'Table'[Description]
    ),
    [Highest Non Engineering Estimate]
)

You need to put in the SUMMARIZE enough columns to uniquely identify a row. If there is a unique ID in your dataset then you could just use that column.

[JMcAnarney1]

So here is what I have feeding into eachother:

Highest Non Engineering Estimate = Calculate(SUM('Bid Template Data'[Total Cost]),'Bid Template Data'[Bidder] <> "Engineering Estimate")

This is summing together possible bids that that do not represent an estimate which needs to be split out

the summarzing function is doing this but in formula above it is not showing highest still:

Highest Bid =

MAXX (

    SUMMARIZE (

        'Bid Template Data',

        'Bid Template Data'[Bid Event],

        'Bid Template Data'[Bidder],

        'Bid Template Data'[Description.1]

    ),[Highest Non Engineering Estimate])

Which gets me the results: left is output right is what the output should be if highest which is 31,853

I think probelm is that my Highest non-engineering formula still needs to breakout it further 

[JMcAnarney1]

I should add I know how to get the highest bidder by name but not by amount with the following formula:

TOPN = MAXX( TOPN (1, DISTINCT('Bid Template Data'[Bidder]), Calculate (Sum( 'Bid Template Data'[Total Cost]),'Bid Template Data'[Bidder] <> "Engineering Estimate")),'Bid Template Data'[Bidder])

[johnt75 · Accepted solution]

Its possible that the the SUMMARIZE still isn't at the correct granularity. You can check what is being returned by creating a new table using

Tmp table =
ADDCOLUMNS (
    SUMMARIZE (
        'Bid Template Data',
        'Bid Template Data'[Bid Event],
        'Bid Template Data'[Bidder],
        'Bid Template Data'[Description.1]
    ),
    "Highest estimate", [Highest Non Engineering Estimate]
)

and see what that shows in the data view. You may need to add more columns into the SUMMARIZE