Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Auto-Filter Values in Matrix in different levels in Hierarchy

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...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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 )

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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)

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

My Dataset:

YearRegionPIT_INDConfirmations
2014East CoastTY957.8221
2014West CoastTY574.4138
2015East CoastTY309.0494
2016East CoastTY233.478
2016West CoastTY902.912
2016Mid WestTY852.4647
2017East CoastTY758.103
2018East CoastTY957.1982
2018West CoastTY28.61213
2018Mid WestTY925.1769
2018MexicoTY283.9663
2019East CoastTY496.9914
2019Mid WestTY935.6592
2019West CoastTY549.7434
2019MexicoTY374.5806
2019CanadaTY375.7129
2019EuropeTY0.392929
2014 PITEast CoastLY333.7524
2014 PITWest CoastLY901.5507
2015 PITEast CoastLY812.9767
2016 PITEast CoastLY82.70674
2016 PITWest CoastLY196.2866
2016 PITMid WestLY438.4759
2017 PITEast CoastLY109.5359
2018 PITEast CoastLY632.1703
2018 PITWest CoastLY359.0873
2018 PITMid WestLY747.6088
2018 PITMexicoLY62.25398
2019 PITEast CoastLY289.9601
2019 PITMid WestLY742.7774
2019 PITWest CoastLY305.5905
2019 PITMexicoLY363.5127
2019 PITCanadaLY624.2058
2019 PITEuropeLY763.5378
  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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 )