cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Slicer - Filter only on one data in matrix

Hello,

 

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

My dataset is :

AnnéeLibelleMois numMois LibMontant HT
2018Poitiers1Janvier50
2018Poitiers1Janvier100
2018Poitiers1Janvier200
2018Poitiers2Février50
2018Poitiers2Février150
2018Poitiers2Février256
    
2018Poitiers11Novembre126
2018Poitiers11Novembre147
2018Poitiers11Novembre644
2018Poitiers12Décembre456
2018Poitiers12Décembre236
2018Poitiers12Décembre654
2019Poitiers1Janvier50
2019Poitiers1Janvier100
2019Poitiers1Janvier200
    
2019Poitiers6Juin50
2019Poitiers6Juin100
2019Poitiers6Juin

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.

dashboard.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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]
)

Slicer-Filter-only-on-one-data-in-matrix-1.pngSlicer-Filter-only-on-one-data-in-matrix-2.pngSlicer-Filter-only-on-one-data-in-matrix-3.png

 

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

 

Slicer-Filter-only-on-one-data-in-matrix-4.pngSlicer-Filter-only-on-one-data-in-matrix-5.png

 

 

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

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

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

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]
)

Slicer-Filter-only-on-one-data-in-matrix-1.pngSlicer-Filter-only-on-one-data-in-matrix-2.pngSlicer-Filter-only-on-one-data-in-matrix-3.png

 

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

 

Slicer-Filter-only-on-one-data-in-matrix-4.pngSlicer-Filter-only-on-one-data-in-matrix-5.png

 

 

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

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

View solution in original post

Highlighted
Anonymous
Not applicable

Thanks

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors