Slicer - Filter only on one data in matrix

[Anonymous]

Hello,

I user matrix to calculate the sum of towns sales by year.

My dataset is :

Année	Libelle	Mois num	Mois Lib	Montant HT
2018	Poitiers	1	Janvier	50
2018	Poitiers	1	Janvier	100
2018	Poitiers	1	Janvier	200
2018	Poitiers	2	Février	50
2018	Poitiers	2	Février	150
2018	Poitiers	2	Février	256
…
2018	Poitiers	11	Novembre	126
2018	Poitiers	11	Novembre	147
2018	Poitiers	11	Novembre	644
2018	Poitiers	12	Décembre	456
2018	Poitiers	12	Décembre	236
2018	Poitiers	12	Décembre	654
2019	Poitiers	1	Janvier	50
2019	Poitiers	1	Janvier	100
2019	Poitiers	1	Janvier	200
…
2019	Poitiers	6	Juin	50
2019	Poitiers	6	Juin	100
2019	Poitiers	6	Juin	200

I created 2 measures to calcultate the sum of sales :

CA_2018 = CALCULATE(
             SUM(Data[Montant HT]);
             FILTER(Data;Data[Année ]=MIN(Data[Année ])
             )
             )

To calculate the sum of sales of min year (2018).

CA_2019_1 = IF (HASONEVALUE(Data_test[Année ]);
                BLANK();
                CALCULATE(
                    SUM(Data_test[Montant HT]);
                    FILTER(Data_test;Data_test[Année ]=MAX(Data_test[Année ])
                )
                ))

To calculate the sum of sales of max year 2019.

I created a slicer to dynamically filter months lib.

But if i modify slicer selection, there is an update of 2018 calculate.

I would like that 2018 calculation not to be affected.

If i add the year with month, that's ok but i prefer don't use this solution.

Thanks 

Yvon

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

You can use the following DAX to create measure that are not effected by month slicer, but the Name Column in table visual will still effected by the month. But the value of 2018 is always the full year.

SUM_2018 = 
SUMX (
    FILTER (
        ALL ( 'Table' ),
        AND (
            'Table'[Year] = MINX ( ALL ( 'Table' ), [Year] ),
            [Name] = SELECTEDVALUE ( 'Table'[Name] )
        )
    ),
    [Value]
)

If you want to see all the name in this table always, just turn off the effect by this slicer.

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Anonymous]

Thanks