To hide all subtotals & totals by filtering


If you would like to hide subtotals and totals, and see only the items on the lowest level of a hierarchy, you can use a filter on a boolean line item. Add a line item called "Filter." Set the format to Boolean, and the summary to None. Then enter "true" in the formula bar. You probably then want to hide the line item in the grid view. Set the filter by clicking the filter icon, and then choose the tab for the dimension you would like to filter (i.e. customers). Select the model name, and the line item, "Filter," and then set to show all values that are selected. You should now only see the lowest level of the hierarchy, as all subtotal and totals will not be selected in the boolean, and will therefore be filtered out.  You may also do the opposite (filter out only the lowest level of a hierarchy), by creating a second boolean line item, and setting the value equal to "Not 'Filter'" (if 'filter' is what the first boolean line item is called). Set the summary to Formula. Now set the filter in the same way, but filter on this second line item. 


Best Answer

  • PrevContributor
    yes, great tip, very often used both for displaying relevant information and also for exporting data according to requirement.

    If you have Time and Line Items as nested dimensions in column, and that you want to filter on Time to display only details 'days, weeks, months) I had to create a second module to place the filter on. I was not able to filter by using a line item within the same module.  But it works fine if filtering on an item from another module (dedicated to filtering).

    Kind regards.


  • Great tip, would also be good if formulas could be extended to properties of the list so you could do it all in the properties area (rather than a module)
  • Very good tip!! Thanks a lot!!