5 Replies Latest reply on Oct 8, 2018 2:01 PM by Joe Oppelt

    Turning off filter for one measure

    Lea Schafer

      Hello,

       

      I have created a line chart that shows the number of students who are considered 'Tier 3' in absences, meaning they have missed a certain percentage of school days that have occurred via a filter and calculated field. This chart was created without a problem but now I would like to take that one step further and take the number of kids who are Tier 3 and make it a percent out of the total enrollment. When I add enrollment in as the denominator of the calculation, it keeps the filter attached and I get a 1 returned (because all the kids who are Tier 3 are in fact currently enrolled).

       

      I'm hoping there is a way to denote that the enrollment measure should not have the filter applied, and rather just be the total sum of students enrolled.

       

      Thank you!

        • 1. Re: Turning off filter for one measure
          Joe Oppelt

          A FIXED LOD calc gets evaluated by Tableau's order of operations BEFORE any filters get applied to your sheet.


          So:

           

          { FIXED : SUM([whatever]) }

           

          or

           

          { FIXED : COUNTD([something]) }

           

          ... will contain the respective value of that calc over the full set of rows from your data set.

          • 2. Re: Turning off filter for one measure
            Lea Schafer

            fixed.JPG

            Am I doing this correctly? When I in put this it says this is an invalid calculation.

            • 3. Re: Turning off filter for one measure
              Joe Oppelt

              I can't vouch for the correctness of the results, but the FIXED calc is sort of like a single measure value (in its treatment by Tableau).  It's a number that gets associated with every row in the data.  (Same value gets tacked on internally onto every row.)  So because the COUNT in the numerator is an aggregate, you need to make the FIXED part of the denominator an aggregate too.

               

              So:

               

              COUNT([SystemStudentID]) / ATTR( { FIXED : SUM([Enrollment]) } )

               

              That should take care of the syntax error.

               

              Do you know that { FIXED : SUM([Enrollment]) }  is evaluating to the value you expect it to be?  Consider (for testing purposes) displaying it first and see what the number is.  (Make a calc that is just that shorter calc first, and display it in the title or on rows or something.)  And is [Enrollment] an aggregate?  If it's just a measure or a dimension coming in from the data source, then you can do a FIXED on it.  But if it's already an aggregate calc you won't be able to do a FIXED calc on it.  (I'm just trying to anticipate some of the follow-up questions that might arise.)

              • 4. Re: Turning off filter for one measure
                Lea Schafer

                First, thank you for your help!

                 

                Second, Enrollment is a measure directly from the data source and I used it in another sheet for something else so it does measure what I want. However I failed to realize that I still need some filters on it. In this instance, I need the enrollment of just one campus of our district for the past 5 years. When I used the calculation (which did work) I am getting total enrollment at all schools and also including years I didn't want to look at, which is throwing off the percentages I'm trying to obtain. I have the individual data from other sheets due to calculations and filters applied to them and I'm beginning to wonder if this is just something I'll have to math-by-hand in Excel and upload into Tableau.

                • 5. Re: Turning off filter for one measure
                  Joe Oppelt

                  { FIXED [Campus], [Year *] : SUM([Enrollment]) }

                   

                  This will give you the same behavior, but at the detail level specified.  (Thus the name for these calcs:  LOD = Level Of Detail.)

                   

                  Note the [Year] calc.  If you have a dimension that breaks out years, the that's what you'll use there.  If you have YEAR([Date]) on the sheet, then drag that pill into that place in your calc editor and Tableau will give you the correct syntax to grab YEAR from a Date field as a specific dimension.  (I hope I described that adequately.)

                   

                  So once you have the calc being evaluated at those levels, if you filter years and campus on the sheet, Tableau will grab the sums for just those dimensional values.