Cumulative Measure not filtering out the data while using on a particular filter

[Anonymous]

Hello Community,

I have created a measure to calulate the cumulative values of the column named actual. 
for the cummulative I wrote the below measure.

Cumulative_Actual = 
CALCULATE (
    SUM ( 'TC-Forecast Road Map'[Actual]),
     FILTER (
        ALL ( 'TC-Forecast Road Map' ),
        
        'TC-Forecast Road Map'[Year Month] <= MAX ( 'TC-Forecast Road Map'[Year Month]  )
    )
) 

and this is the values I get. here 129 is the cumulative of all sites. and 123 is the sum of all trainings

But when I try to put the Hornell filter it is not being appiled on the Cummulative Actual Measure and its Showing 129 instead of 123.

Please help me with this issue. and correct me if I'm wrong anywhere.

Thanks.!!

[Anonymous · Accepted solution]

Hi @Anonymous ,

To solve this problem, I suggest to first create a new table with the below measure, and link the site element of the new table to the corresponding counterpart of the original table.

Table = DISTINCT('TC-Forecast Road Map'[Site])

This step is primarily served to avoid potential conflicts during filtering.

Next, we use IF function to modify the measure.

Here is an example:

Cumulative_Actual2 = 
IF (
    ISFILTERED ( 'Table'[Site] ),
    CALCULATE (
        SUM ( 'TC-Forecast Road Map'[Actual] ),
        FILTER (
            ALLSELECTED ( 'TC-Forecast Road Map' ),
            [Site] = MAX ( 'TC-Forecast Road Map'[Site] )
                && 'TC-Forecast Road Map'[Year Month] <= MAX ( 'TC-Forecast Road Map'[Year Month] )
        )
    ),
    CALCULATE (
        SUM ( 'TC-Forecast Road Map'[Actual] ),
        FILTER (
            ALL ( 'TC-Forecast Road Map' ),
            'TC-Forecast Road Map'[Year Month] <= MAX ( 'TC-Forecast Road Map'[Year Month] )
        )
    )
)

Here is a picture before selecting Hornell.

By the way, I added an INDEX column

And here is the final result after selecting Hornell.

As you can see from the picture, now the all values under the column of Cumulative_Actual becomes 123 instead of 129.

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.           

[lbendlin]

That's not what "cumulative" normally means. 

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...