Forum Discussion

46Days's avatar
46Days
New Member
3 years ago
Solved

Error when using cumulative sum with activated filters

I found a solution to my problem but I don't know why the cumulative sum is not working.

 

I have the following table which brings me some data which I want to add cumulatively, the question is that when filtering is not applying the cumulative sum function.

 

 

following the above logic, the table with the filter enabled should look like this

 

The function I found and am using is the following

Cumulative_Revenue = CALCULATE(SUM('Hoja1 (2)'[Value]), FILTER(ALLSELECTED('Hoja1 (2)'[# Month]), YEAR('Hoja1 (2)'[# Month])=YEAR(MAX('Hoja1 (2)'[# Month]))&&'Hoja1 (2)'[# Month] <= MAX('Hoja1 (2)'[# Month])))

 

I would appreciate if you can help me to find the error why it is not adding correctly cumulatively with the filter activated.

General Comment
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, 46Days 

     

    You can try the following methods.

    Measure =
VAR _Cumulative1 = CALCULATE ( SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ),
            [# Month] <= SELECTEDVALUE ( 'Table'[# Month] )
                && [Service] = SELECTEDVALUE ( 'Table'[Service] ) ) )
VAR _Cumulative2 = CALCULATE ( SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ),
            [# Month] = SELECTEDVALUE ( 'Table'[# Month] )
                && [Service] <= SELECTEDVALUE ( 'Table'[Service] ) ) )
RETURN
    IF ( ISFILTERED ( 'Table'[Month] ), _Cumulative2, _Cumulative1 )

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

     

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, 46Days 

     

    You can try the following methods.

    Measure =
VAR _Cumulative1 = CALCULATE ( SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ),
            [# Month] <= SELECTEDVALUE ( 'Table'[# Month] )
                && [Service] = SELECTEDVALUE ( 'Table'[Service] ) ) )
VAR _Cumulative2 = CALCULATE ( SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ),
            [# Month] = SELECTEDVALUE ( 'Table'[# Month] )
                && [Service] <= SELECTEDVALUE ( 'Table'[Service] ) ) )
RETURN
    IF ( ISFILTERED ( 'Table'[Month] ), _Cumulative2, _Cumulative1 )

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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