Wrong accumulated values total sum

[Andregewehr]

I have the following table with a accumulate value measure:

_accumulate_value =SUMX(VALUES('Table'[date]),

CALCULATE(sum('Table'[value]),FILTER(ALL('Table'[date]),[date]>=MAX('Table'[date]))))

id_product	date	sells	value	_accumulate_value
1	10/07/2022	5	10	10
1	09/07/2022	10	10	20
1	08/07/2022	6	10	30
1	07/07/2022	10	6	36
1	06/07/2022	0	0	36
1	05/07/2022	0	2	38
1	04/07/2022	8	10	48
1	03/07/2022	1	10	58
1	02/07/2022	6	10	68
1	01/07/2022	10	10	78
		56	78	422

But the total for accumulate_value is returning wrong in power bi

How can i fix it to get the total sum 422 instead of 100?

[AntonioM · Accepted solution]

You could create one more measure

Measure = 
IF(
    HASONEVALUE('Table'[date]),
    [_accumulate_value],
    SUMX(
        VALUES('Table'[date]),
        [_accumulate_value]
    )
)

which gives you 422

[Andregewehr]

Perfect! Thanks!

[Jihwan_Kim]

Hi,

Please try the below measures and the attached pbix file.

both of them should work.

_accumulate_value V2 =
SUMX (
    VALUES ( 'Table'[date] ),
    CALCULATE (
        CALCULATE (
            SUM ( 'Table'[value] ),
            FILTER ( ALL ( 'Table'[date] ), 'Table'[date] >= MAX ( 'Table'[date] ) )
        )
    )
)

_accumulate_value = 
SUMX (
    VALUES ( 'Table'[date] ),
    CALCULATE (
        SUMX (
            VALUES ( 'Table'[date] ),
            CALCULATE (
                SUM ( 'Table'[value] ),
                FILTER ( ALL ( 'Table'[date] ), [date] >= MAX ( 'Table'[date] ) )
            )
        )
    )
)

[amitchandak]

@Andregewehr , use allselected

_accumulate_value =SUMX(VALUES('Table'[date]),
CALCULATE(sum('Table'[value]),FILTER(allselected ('Table'[date]),[date]>=MAX('Table'[date]))))