How to hide subtotals even if subtotals is disabled || Measures || Hierarchy

[Anonymous]

Re: Hide a column in the Matrix but not in total  Hierarchy Slicer visual not working after September 2019 update 
I have a use case in which I need to hide all the subtotals being calculated at all hierarchies and show the values only at the very lowest level of hierarchy when the visual is completely expanded.

The issue in my dashboard is, whenever I try to collapse the dashboard, for each collapsed field it is calculating some value and showing it as resultant based on the entire underlying hierarchy. As I've applied some conditional formatting(icons) based on the measure and because of this calculation, incorrect values/symbols are getting populated at each level of hierarchy.

Note: I'm populating  a measure column as value in my matrix visual.

I've tried disabling subtotals/totals to Off in Formatting tab. But still the values are coming up in the visual.

Current view:
-abcd             6.7%(Approx)
    -abcd         33%
         abcd      100% 
         bcdf       100%
         cdef       100%

    -abcd         33%
         abcd      100% 
         bcdf       100%
         cdef       100%

Outcome must be :

-abcd             
   -abcd         
         abcd      100% 
         bcdf       100%
         cdef       100%

   -abcd         
         abcd      100% 
         bcdf       100%
         cdef       100%

Any quick resolutions are really helpful

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Try this measure

Measure = IF(HASONEVALUE('Table'[SUB CATEGORY]),SUM('Table'[VALUES]),BLANK())

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , In matrix you have an option  "Per Row Level" under subtotal enable that, and then you disable subtotal based on each row column.

[Anonymous]

@amitchandak , I couldn't see any option named 'Per Row level' in my subtotals section.

[amitchandak]

@Anonymous , subtotals are off, switch on and check

[Anonymous]

@amitchandak , Tried that Able to see enable per row level option and tried the way which you suggested by enabling rows subtotal -> Enable per row level -> disabling row subtotals. It still didn't work for me. I'm able to see the subtotals again.
As I've mentioned before, I'm populating a measure column and the measure is getting applied at all hierarchies and automatically performing the same calculation which is defined specifically for the lowest hierarchy which has actual data.

I strongly suspect that this calculation after collapsinng the view is mainly because of the Measure column which gets auto applied at all levels. Please find the sample data in the question description.

[PaulDBrown]

@Anonymous 

Let's assume your measure is [% calc] and the lowest level of the hierarchy is Table [column 3].
Try

hide others = IF(ISINSCOPE(Table [column 3]), [% calc])

[servemn]

Thanks @Anonymous your solution works perfectly!