Auto-Filter Values in Matrix in different levels in Hierarchy

[Anonymous]

Hi,

I have a matrix with a two variables in the rows.  The first level variable is year. The 2nd variable is month but i only want the values to display data for the current year.

For example - at level 1 the row will be years, and the values will by confirmations for all those years. At level 2, the rows will be months, and the values will be confirmations for the current year only.

Is it possible to only filter for the current year when i have month as the rows in the hirarchy? 

[v-frfei-msft]

Hi @Anonymous ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

[Anonymous]

Hi,

I inserted 3 tables below. The first table would hypothetically be the default view of the matrix (This data is unfiltered). The second below table would be the matrix, but drilled down one level(This data would be filtered for PIT_IND=TY)

The 3rd below table is my dataset. 

Does this make sense? 

Level 1: Data isn't Filtered:

Year	Confirmations
2014	1,532
2014 PIT	1,235
2015	309
2015 PIT	813
2016	1,989
2016 PIT	717
2017	758
2017 PIT	110
2018	2,195
2018 PIT	1,801
2019	2,733
2019 PIT	3,090
Grand Total	17,282

Level 2 (Filtered for PIT_IND=TY)

Region	Confirmations
Canada	376
East Coast	3,713
Europe	0
Mexico	659
Mid West	2,713
West Coast	2,056
Grand Total	9,516

My Dataset:

Year	Region	PIT_IND	Confirmations
2014	East Coast	TY	957.8221
2014	West Coast	TY	574.4138
2015	East Coast	TY	309.0494
2016	East Coast	TY	233.478
2016	West Coast	TY	902.912
2016	Mid West	TY	852.4647
2017	East Coast	TY	758.103
2018	East Coast	TY	957.1982
2018	West Coast	TY	28.61213
2018	Mid West	TY	925.1769
2018	Mexico	TY	283.9663
2019	East Coast	TY	496.9914
2019	Mid West	TY	935.6592
2019	West Coast	TY	549.7434
2019	Mexico	TY	374.5806
2019	Canada	TY	375.7129
2019	Europe	TY	0.392929
2014 PIT	East Coast	LY	333.7524
2014 PIT	West Coast	LY	901.5507
2015 PIT	East Coast	LY	812.9767
2016 PIT	East Coast	LY	82.70674
2016 PIT	West Coast	LY	196.2866
2016 PIT	Mid West	LY	438.4759
2017 PIT	East Coast	LY	109.5359
2018 PIT	East Coast	LY	632.1703
2018 PIT	West Coast	LY	359.0873
2018 PIT	Mid West	LY	747.6088
2018 PIT	Mexico	LY	62.25398
2019 PIT	East Coast	LY	289.9601
2019 PIT	Mid West	LY	742.7774
2019 PIT	West Coast	LY	305.5905
2019 PIT	Mexico	LY	363.5127
2019 PIT	Canada	LY	624.2058
2019 PIT	Europe	LY	763.5378

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a meausre as below.

Measure = 
VAR sum1 =
    CALCULATE ( SUM ( Table1[Confirmations] ) )
VAR filtersum =
    CALCULATE (
        SUM ( Table1[Confirmations] ),
        FILTER ( Table1, Table1[PIT_IND] = "TY" )
    )
RETURN
    IF ( ISINSCOPE ( Table1[Region] ), filtersum, sum1 )