Total of variance column current year vs last year in Matrix visual

[Majidbhatti]

Hi All

Can anyone help me in below. I have a page with Years filter and want to add Matrix as below. I want that user choose any 2 years and the Matrix should be formatted as below. 

I have added formula of  [VAR A = B of 2018  - A of 2019] but its repeating the columns as below. I know i can resize the column but there must be a simplier way to handle this other than resizing columns.

[jdbuchanan71]

Hello @Majidbhatti 

You could use

IF ( ISINSCOPE ( Dates[Year] ), BLANK(), [your measure here] ) 

Which will blank out the measure under the years and only show it on the total columns.

[Majidbhatti]

Hi, So this will show blank column but it will not hide the column right? how to hide these blank columns?

[jdbuchanan71]

The only way I can think to do it would be to write 6 measures

Prior Year A

Prior Year B

Current Year A

Current Year B

VAR A

VAR B

Then you add the 6 measures without having year on the columns.

[Majidbhatti]

@jdbuchanan71 ,  I tried you solution but it does not seems to work in Matrix. ISINSCOPE doesn't sense the YEAR in the Matrix. 

Moreover, creating measures will also not help as i want all the columns of Last year compared with  Current Year and then variance of all the columns.

[jdbuchanan71]

Then you will have to resize the columns to hide the extras.

[Majidbhatti]

Resizing is also not working because i have a filter where user can select YEARS. so if i select 2018 & 2019 and resize columns of 2018. later when user selects 2019 & 2020, those columns are not hidden anymore.

[jdbuchanan71]

Can you share your .pbix file?  If there is sensitive information you can anonymize it before sharing.

[Majidbhatti]

@jdbuchanan71  sorry i tried a lot to strip the data but this report contains confidential data for last two year with file size of 200MB. so dont know how i can share