Forum Discussion

JMcAnarney1's avatar
JMcAnarney1
Icon for Helper I rankHelper I
3 years ago
Solved

Question on finding Max for Sum per category?

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's avatar
    johnt75
    3 years ago
    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.

  • johnt75's avatar
    johnt75
    3 years ago

    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

7 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You can use

    Max Non Engineering Estimate =
MAXX ( VALUES ( 'Table'[Bid event id] ), [Highest Non Engineering Estimate] )
    • JMcAnarney1's avatar
      JMcAnarney1
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago
        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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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