Remove column in Matrix but not in total

[VHosamane]

I have matrix with 3 columns (1st column is raw data and 2nd and 3rd column is calculated column) which is repeated for every month. Then, I have a sum for the 3 columns by quarter & by year. I want to hide 2nd of the 3rd columns for every month & only show the sum per quarter & year. Is there a way to do it in Power BI?

I have tried hiding by formatting (wrapping text & decreasing the column width to min). It's an ok soution but not ideal. Any other options?

[Cmcmahan · Accepted solution]

So you want the total for Column1 every month, quarter, and year, but only want totals for Column2 and Column3 every quarter and year?  

The best way to handle this is to use ISINSCOPE.  You'll create a measure for your 2nd and 3rd column to use in the matrix instead of the raw values. Being able to see the matrix layout you're talking about would help, but here's my best guess as to what it would look like :

Column2ForMatrix = 
    IF(ISINSCOPE(Date[Day]), SELECTEDVALUE(Table[Column2]),
        IF(ISINSCOPE(Date[Month]), BLANK(),
            SUM(Table[Column2]) 
        )
    )

Then you would use [Column2ForMatrix] instead of [Column2] as the field in the values bucket of your visual pane. Do the same thing for [Column3].

What's happening here is that ISINSCOPE is checking if the current scope is the [Day] (your lowest level, I assume) and  giving you the single value.  Then it checks if the current scope is the [Month], and returns a blank.  Then for any other scopes, it returns a SUM of the column.

If this solution doesn't work, could you share screenshots of the matrix/data tables or the .pbix file you're working with?