Need some help to create a measure to total max value of daily sum

[tanj1]

I'm new to Power BI. I need some help to create a measure to sum up the max "ORADailyPreTotal" of each client in the table below. I would like to sum up only the maximium of each client daily sum which is 6877.909871 + 6675.3583845. Thanks a lot in advance.

[v-stephen-msft · Accepted solution]

Hi @tanj1 ,

Try the following measure. The idea is to create a virtual table, filter out the maximum values from the virtual table, and then add them together.

Result =
VAR _table =
    ADDCOLUMNS (
        'Table',
        "max",
            CALCULATE (
                MAX ( 'Table'[ORADailyPreTotal] ),
                FILTER ( ALLSELECTED ( 'Table' ), [client] = EARLIER ( 'Table'[client] ) )
            )
    )
RETURN
    SUMX ( FILTER ( _table, [max] = [ORADailyPreTotal] ), [ORADailyPreTotal] )

Best Regards,

Stephen Tao

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

[tanj1]

Thanks a lot Stephen Tao 😉

[tanj1]

Thanks a lot Stephen Tao!😉

[tanj1 · Accepted solution]

Hi Stephen & Pete, apprecaite all your help.

Somehow I'm not able to get it work for the last few days. 

I tried using the above formular but somehow still can't get it work.

I would like to get the result of 14802.74685 from this CSV file.  

Here is the CSV File 

[v-stephen-msft]

Hi @tanj1 ,

Create a daily measure.

Daily =
CALCULATE (
    SUM ( data[Pre GB] ),
    FILTER (
        ALLSELECTED ( data ),
        [Year] = MAX ( 'data'[Year] )
            && [client] = MAX ( 'data'[client] )
            && [Day] = MAX ( 'data'[Day] )
            && [Month] = MAX ( 'data'[Month] )
    )
)

Then create another measure to filter the max value.

Max =
IF (
    MAXX (
        FILTER (
            ALLSELECTED ( data ),
            [Year] = MAX ( 'data'[Year] )
                && [Month] = MAX ( 'data'[Month] )
                && [client] = MAX ( 'data'[client] )
        ),
        [Daily]
    ) = [Daily],
    [Daily],
    BLANK ()
)

There is also a max value in March, but your expected result is 14802.75. Do you want to calculate the sum of the maximum values only in February?

Best Regards,

Stephen Tao

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

[tanj1]

Thanks Stephen. Almost getting there 😀.

I would like to get the max of any date for BBB and AAA and sum them together. 

[v-stephen-msft]

Hi @tanj1 ,

OK. To add them up. Try

Total=SUMX('DATA', [Max])

Best Regards,

Stephen Tao

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

[v-stephen-msft · Accepted solution]

Hi @tanj1 ,

Try the following measure. The idea is to create a virtual table, filter out the maximum values from the virtual table, and then add them together.

Result =
VAR _table =
    ADDCOLUMNS (
        'Table',
        "max",
            CALCULATE (
                MAX ( 'Table'[ORADailyPreTotal] ),
                FILTER ( ALLSELECTED ( 'Table' ), [client] = EARLIER ( 'Table'[client] ) )
            )
    )
RETURN
    SUMX ( FILTER ( _table, [max] = [ORADailyPreTotal] ), [ORADailyPreTotal] )

Best Regards,

Stephen Tao

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