Filter multiple dimensions as rows


Hi team,

Im working on a rolling forecast module and have the following dimensions Versions (page), Time (columns) and Cost Centre, Gl and line items as rows. one of the line items is an input called temporary variance and a requirements is that across all time that the temporary variance sums to 0.

My problem is, I cannot figure out how to set up a filter that works on time, Cost centre and GL. Everything I try, if a cost centre has a non balancing variance, does not hide the GL's that are balancing. Below is a snip of some test data and my desired result.

Any assistance would be appreciated.


  • @MatthewWilcox. You would need to apply separate filters for your rows and columns. With your rows, is it all lists or is it both lists and line items. E.g. does your module have line items as Pages in the Pivot (screenshot 1)?

    screenshot 1

    If yes then you only need to have one filter item in your row Filter. If no, then you would have to setup a filter for every line item that is displayed in the rows which I'm assuming P&L Value … up to Adjusted P&L. You need to ensure the following parameters are setup. Refer to screenshot 2

    1. Set "Show Items that match" to any (as per screenshot below highlighted in yellow with number 1 against it)
    2. Enter the all the line items to which you are checking variance conditions
    3. Set the Time to "All periods" highlighted in yellow)

    Screenshot 2

    You will need to a similar configuration for the Time tab

    Hope this helps

  • Another option would be to create a line item subset using the number line items and have a new module with the LISS as dimension.

    Then it's have line items which are not zero with summary set to formula, then another which is then a NOT formula based on all time periods and top of dimensions.

    Effectively as suggested as above but as a module to show the outcomes. Then you can use that to run the filters.