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.

[Anonymous · 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 

[Anonymous]

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. 

[Anonymous]

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.

[Anonymous · 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.

[emm980]

Hi Stephen, I'm going to open again this thread since it is not appliable to my case.

I've a Back End product from which Two final Products came (for instance 1 same frame to have 2 distinct clock). Now, I've the Stock of this Back End product (for instance 1000) and relevant orders that covers only 200 pcs. I've to count the delta (+800) and valorize them to a final report. In excel it is working (the attached table is an extraction of a table from which a pivot table was created showing me the correct value of these 800pieces not covered by orders). In power bi, applying the measure you're telling above, the results is by four times instead of only once. Is it possible to have the max of all these back end products and then the sum of these maxes?

FIN_PROD	BEG_PROD	Data	STK_BEG_PROD	BKLG_BEG_PROD	UNCO_BEG_PROD	UNCO_VAL_BEG_PROD
PROD1	PRODBE	Q202403	142988	51000	91988	0
PROD1	PRODBE	Q202404	142988	19000	72988	0
PROD1	PRODBE	Q202501	142988	29000	43988	0
PROD1	PRODBE	Q202502	142988	2500	41488	0
PROD1	PRODBE	Q202503	142988	2500	38988	0
PROD1	PRODBE	Q202504	142988	0	38988	1271.572848
PROD1	PRODBE	Q202403	142988	51000	91988	0
PROD1	PRODBE	Q202404	142988	19000	72988	0
PROD1	PRODBE	Q202501	142988	29000	43988	0
PROD1	PRODBE	Q202502	142988	2500	41488	0
PROD1	PRODBE	Q202503	142988	2500	38988	0
PROD1	PRODBE	Q202504	142988	0	38988	1271.572848
PROD1	PRODBE	DEL.CY	142988	0	142988	0
PROD1	PRODBE	Q202403	142988	51000	91988	0
PROD1	PRODBE	Q202404	142988	19000	72988	0
PROD1	PRODBE	Q202501	142988	29000	43988	0
PROD1	PRODBE	Q202502	142988	2500	41488	0
PROD1	PRODBE	Q202503	142988	2500	38988	0
PROD1	PRODBE	Q202504	142988	0	38988	1271.572848
PROD2	PRODBE	Q202403	142988	51000	91988	0
PROD2	PRODBE	Q202404	142988	19000	72988	0
PROD2	PRODBE	Q202501	142988	29000	43988	0
PROD2	PRODBE	Q202502	142988	2500	41488	0
PROD2	PRODBE	Q202503	142988	2500	38988	0
PROD2	PRODBE	Q202504	142988	0	38988	1271.572848
PROD2	PRODBE	Q202403	142988	51000	91988	0
PROD2	PRODBE	Q202404	142988	19000	72988	0
PROD2	PRODBE	Q202501	142988	29000	43988	0
PROD2	PRODBE	Q202502	142988	2500	41488	0
PROD2	PRODBE	Q202503	142988	2500	38988	0
PROD2	PRODBE	Q202504	142988	0	38988	1271.572848