cancel
Showing results for
Did you mean:  Helper I

## Cumulative total with a measure

Hello Community,

I have created a measure that is adding new rows to table with condition.  I want to use that measure to calculate cumulative total.

Here is created measure :

Measure Value =
IF(MAX('table'[Value])<>BLANK(),MAX('table'[Value]),MAXX(FILTER(ALL('table'),'table'[Year Month]=MAXX(ALL('table'),'table'[Year Month])),'table'[Value])*POWER(1.2,DATEDIFF(MAXX(ALL('table'),'table'[Year Month]),MAX('Table (2)'[Year Month]),MONTH)))

Using above measure I want to calculate cummulative total.  I tried with following measure but it is not working properly.

Cummulative Total = SUMX(FILTER (ALLSELECTED('table') , 'table'[Year Month]<=MAX('table'[Year Month] ) ),[Measure Value] )

I have attached data here:

Thankyou in anticipation!
Sam

1 ACCEPTED SOLUTION  Community Support

Hi, @sam_hoccane

Please try formula like:

``````Cummulative Total =
VAR tab =
ADDCOLUMNS ( ALLSELECTED ( 'Table (2)' ), "Measure Value", [Measure Value] )
RETURN
SUMX (
FILTER ( tab, 'Table (2)'[Year Month] <= MAX ( 'Table (2)'[Year Month] ) ),
[Measure Value]
)`````` Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2  Community Support

Hi, @sam_hoccane

Please try formula like:

``````Cummulative Total =
VAR tab =
ADDCOLUMNS ( ALLSELECTED ( 'Table (2)' ), "Measure Value", [Measure Value] )
RETURN
SUMX (
FILTER ( tab, 'Table (2)'[Year Month] <= MAX ( 'Table (2)'[Year Month] ) ),
[Measure Value]
)`````` Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Super User

@sam_hoccane , Try like

Cummulative Total = calculate(SUMX( values('table'[Year Month]),[Measure Value] )
,FILTER (ALLSELECTED('table') , 'table'[Year Month]<=MAX('table'[Year Month] )))

better to use a date table

Cummulative Total = calculate(SUMX( values('Date'[Year Month]),[Measure Value] )
,FILTER (ALLSELECTED('Date') , 'Date'[Year Month]<=MAX('Date'[Year Month] )))

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!   