5 Replies Latest reply on Jan 9, 2019 9:50 AM by Jennifer Roussy

    Create filter/parameter to only sum measures if value is 'true'/1

    Jennifer Roussy

      Hi,

       

      This may be a simple issue and I may be making it more complex than necessary, but I'm having difficulty creating a filter or parameter that only sums values based on the filter/parameter selected.  I've used parameters successfully in other worksheets, but for some reason i can't get this one working.

       

      My data sheet looks like this, where I have each child care center on a different row, a column for each Quarter (Q4 2018 to Q4 2019), and a column for each age category (infant, toddler, etc.).  For the Quarters, a 0 indicates inactive and 1 active.  The values under the age categories indicates the number of licensed spaces for each.

       

      In my worksheet, I have a table that sums the values for each of the age groups, as well as a total space column. 

       

      I need to create either a filter or a parameter for Quarters, so that I can see how many spaces are active in each quarter.  I've tried using a calculated field to create a new dimension to use as the filter, but the problem with this is if i choose Q1 2019, it's only summing values of the sites that are only Q1 2019 = 1 (If Q4 2018 = 1 and Q1 2019 = 1, it's not added to the sum).

              

        

       

      What I want/need is a filter/parameter so that when I choose Q1 2019, it will sum all of the spaces that are active in Q1 2019.  Any help would be greatly appreciated!