How to hide subtotals for few columns in a matrix on power BI desktop?

[Anonymous]

 Within power BI desktop, i am not able to hide few columns subtotals. I can either hide all or show all for subtotals. Is it possible to hide subtotals for few columns and show on others with a matrix?

[kmccraw]

Wondering if there a way to only hide the Avg Days total in the matrix table, circled, below? 

[MNGuy183]

Anyone solve this? I have a calculation that makes sense at the line item level but makes no sense summed up. Is there a way to hide the Total and Subtotals for this?

[Anonymous]

Hi,

You can create a new measure to do this. Inside it you will check if value is equal or higher than minimum with simple if.

That's how I have done this.

Measure= IF(SUM('Table'[some_value])>=(MIN('Table'[some_value])+1);BLANK();SUM('Table'[some_value]))

Instead of +1 you can use smaller value if needed. Depends from your data.

You can do that as well with distinctcount, especially might be needed when using average instread of sum.

Measure= IF(DISTINCTCOUNT('Table'[Country])>1;BLANK();AVERAGE('Table'[some_value]))

Below standard column first and measure in second column

Other option is to play with ALLEXCEPT but this might be harder in regards to slicers

Measure= IF(SUM('Table'[some_value])>=CALCULATE(SUM('Table'[some_value]);ALLEXCEPT('Table';'Table'[date];'Table'[type]));BLANK();SUM('Table'[some_value]))

What you need to remember about is to have the same filters as for your total in view, in my case it was date and type.

Second most important thing is to exclude aggregate of a table, in my case country.

With such measure selecting 2 countries will still show totals.

Hope it will help someone.

[Subhraz]

Hi,

On the same lines I was able to hide subtotal for a numeric and date column.

Numeric Column: Created a measure: 

Measure = IF(SUM('Some Table'[Numeric Column]) > MAX('Some Table'[Numeric Column]), BLANK(), SUM('Some Table'[Numeric Column]))

Date Column: Created another measure:

Measure 2 = IF(MIN('Some Table'[Date Column]) <> MAX('Some Table'[Date Column]), BLANK(), MAX('Some Table'[Date Column]))

Hiding Subtotal selectively in Power BI

Hope this helps.

Regards,

Subhraz

[morteza]

Yes, This would definitely work for hiding unnecessary totals/subtotals like dates, names, etc. on matrix columns. However if your Matrix rows contain Groups/Hirearchies then you will come up with all the line item in your table appearing in each group: 

For example I created the following measure to hide account numbers in subtotals and Total:

Account No =

IF(MIN(Customers[ACCOUNTNO])<>MAX(Customers[ACCOUNTNO]),BLANK(),MAX(Customers[ACCOUNTNO]))

The measure works fine as long as groups are collapsed:

 However when I expand each of the groups all the data that exists for that particular group in the table will appear in the report, with blank columns:

Expanding first Hierarchy (account managers); should only show one record

does anyone have a solution to overcome with this issue?

Thanks

[morteza]

I just found the solution:

I used the following measure for Account number:

Account No =

IF(HASONEVALUE('Unpaid Invoices'[INVOICEID])=TRUE(),MAX(Customers[ACCOUNTNO]),BLANK())

the input for HASONEVALUE() function must be the lowest hirerarchy level or have the most atomic granularity in your matrix table (Invoice ID with my case).

Highest Group level : CustomersExpanding the Group to 2nd Level: Account ManagersLowest level of hierarchy: Invoices (Invoice ID)

[Anonymous]

I have the same issue. I don't think the March 17 Preview addresses the ability to selectively hide some subtotal columns.

Is there anything new that might address this?