what can the slicer filter and what cannot

[Anonymous]

Hi everyone, I am a bit confused about what can the slicer filter and what cannot in the visualization.

I created a measure which calculates the cumulative sales revenue:

Cumulative Sales Actual =
CALCULATE (
    SUM('Actual data'[Net sales revenue]),
    FILTER (
        ALL ( 'Actual data'),
        ('Actual data'[Period/year] <= MAX ( 'Actual data'[Period/year] )) && (YEAR('Actual data'[Period/year]) = YEAR( MAX ('Actual data'[Period/year])))
    )
)

In the same table, I have a column called Division. I add the slicer of Divsion and try to get the cumulative sales actual for specific division, but find that the Cumulative Sales Actual doesn't change. But if I add slicer for Period.year, the cumulative sales actual changes. So I am confused with the algorithm with the slicer. Can anyone help me explane it?

[Daniil · Accepted solution]

The results you see have nothing to do with the slicer.

When you write ALL('Actual data'), you remove all filters from the table. Therefore, slicing by 'Actual data'[Division] has no effect on the calculation.

The reason why you see different results when you slice by 'Actual data'[Period/year] is that MAX('Actual data'[Period/year]) is evaluated in the original filter context, before you override it with ALL. Hence, changing the value of 'Actual data'[Period/year] influences the calculations.

I would change your formula to the following one:

Cumulative Sales Actual =
CALCULATE (
    SUM ( 'Actual data'[Net sales revenue] ),
    FILTER (
        ALL ( 'Actual data'[Period/year] ),
        ( 'Actual data'[Period/year] <= MAX ( 'Actual data'[Period/year] ) )
            && (
                YEAR ( 'Actual data'[Period/year] )
                    = YEAR ( MAX ( 'Actual data'[Period/year] ) )
            )
    )
)

[Anonymous]

Thank you very much! I finally understand!