cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tanj1
Frequent Visitor

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

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.

tanj1_2-1647710047515.png

 

 

 

2 ACCEPTED SOLUTIONS
v-stephen-msft
Community Support
Community Support

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

 

vstephenmsft_0-1647915385002.png

 

 

 

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.

View solution in original post

tanj1
Frequent Visitor

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 Picture.JPG

View solution in original post

7 REPLIES 7
tanj1
Frequent Visitor

Thanks a lot Stephen Tao 😉

tanj1
Frequent Visitor

Thanks a lot Stephen Tao!😉

tanj1
Frequent Visitor

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 Picture.JPG

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

vstephenmsft_0-1648015854714.png

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.

Thanks Stephen. Almost getting there 😀.

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

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
Community Support
Community Support

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

 

vstephenmsft_0-1647915385002.png

 

 

 

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Kudoed Authors