Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
1 year ago
Solved

SUMMING MAXIMUM VALUES

Hello community, I need help with a report.

I have a table where I want to add maximum values from a table, but instead of adding the values it results in the maximum value of what I'm filtering. For example: I need to get 390 in planned and not 378 as it currently appears. I need to add up the maximum values for each area by filtering the zone and date.

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Syndicate_Admin , hello Alex_Sawdo, thank you for your prompt reply!

     

    Please verify the following measure:

    MO = SUMX(
    SUMMARIZE(
        MARCACIONES,
        MARCACIONES[AREA],
        MARCACIONES[FECHA],
        "MaxPlanned", MAX(MARCACIONES[MO])
    ),
    [MaxPlanned]
)

    Result for your reference:

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    1 year ago

    By the way, the formula I'm using is:

    Measure =
    VAR max_ =
    CALCULATE (
    MAX ( MARKINGS[MO] ),
    FILTER (
    ALL ( MARKINGS ),
    DIALS[DATE] = SELECTEDVALUE (MARKINGS[DATE])
    && MARKINGS[AREA] = SELECTEDVALUE ( MARKINGS[AREA] )
    )
    )
    VAR _if =
    IF (
    MAX ( MARKINGS[MO] ) = max_,
    MAX ( MARKINGS[MO] ),
    BLANK ()
    )
    RETURN
    _if
  • Alex_Sawdo's avatar
    Alex_Sawdo
    Resolver II
    1 year ago

    I don't know how your data is structured, however this is a perfect case to use SUMX: 

     

     

    CALCULATE(
    SUMX(
        SELECTCOLUMNS(
            MARKINGS,
            MARKINGS[AREA],
            MARKINGS[FECHA],
            MARKINGS[ZONIA],
            "V_Calc",
            MAX(MO)
        ),
        V_Calc
    ),
    FILTER(
        MARKINGS,
        AND(
             DIALS[DATE] = SELECTEDVALUE (MARKINGS[DATE]),
             MARKINGS[AREA] = SELECTEDVALUE ( MARKINGS[AREA] )
        )
    )
)

     

     

    This DAX will find the MAX value of MO for the selected columns, then SUM that value. This may not work 100% in your case, however I'd suggest investigating the SUMX function and the SELECTCOLUMS function to help solve this problem.