6 Replies Latest reply on Feb 13, 2017 2:56 PM by Marisel Ruiz

    Filter that keeps Grand Total as denominator

    Ed Kukec

      Hi All,

       

      I have a simple data table that has a count by area and I have a grand total at the bottom. I compute a % of Grand Total as an additional column. I've added a filter for the "Area" but I noticed that when I select one area of interest on the filter the % of Grand total ends up being 100% because the application of the filter removes all the other areas from the Grand Total. What I need is some code or calculation that retains the Grand Total no matter what I select in the filter...almost as if the filter only shows you a view but the underlying Grand Totals remain Intact.  Here's an example of my table.

       

      Area DescriptionCount% of Total
      Area 144.3%
      Area 277.5%
      Area 333.2%
      Area 42324.7%
      Area 51415.1%
      Area 61617.2%
      Area 72628.0%
      Grand Total93100.0%

       

      If I use the Area Description Filter and deselect "All" and only select "Area 4", I would like to get the following:

       

      Area DescriptionCount% of Total
      Area 42324.7%
      Grand Total93100.0%

       

      Any help would be appreciated.

       

      Thanks,

      Ed

        • 1. Re: Filter that keeps Grand Total as denominator
          Joshua Milligan

          Ed,

           

          The reason you are seeing this is that the filter is evaluated first (at the database level), then the percent is calculated after the data is returned to the Tableau Data Engine.

           

          So, the trick is going to be to apply a filter after the data is returned.  You can do this by filtering a field that is a table calculation.  So, in the attached workbook (which is evaluated after the data is returned from the database), I created a calculated field:

           

          LOOKUP(ATTR([Area Description]), 0)

           

          And then put that on the filter shelf.  That filter is then applied after the data is returned, thus it does not affect the percentages.

           

          Joshua

          • 2. Re: Filter that keeps Grand Total as denominator
            Ed Kukec

            That worked well, thanks Joshua.

             

            Ed

            • 3. Re: Filter that keeps Grand Total as denominator
              Girish Sherikar

              Thanks Ed. I too had a similar requirement and your solution worked. However I want to provide for additional filters. So expanding on the example above if there was also a Region attribute associated with each Area, and if I were to provide filtering by Region or specific areas, how can that be accomplished? I am thinking that there must be some kind of 'Or' logic involved, just don't know how/if that can be coded. Following possibilities arise:

              1. At least 1 Area selected but no Region selected - Shows only on selected Areas

              2. At least 1 Area selected and 1 or more regions selected - Shows all selected Regions and only the Areas selected

              3. All Areas selected and 1 or more regions selected - Shows Areas of the selected Regions

              4. No Area Selected and No Region Selected (nothing to report)

              • 4. Re: Filter that keeps Grand Total as denominator
                Girish Sherikar

                I think I figured one part of it. Missed the fact that LOOKUP function has index or position of column that one can specify. So that solved the logic for filter by Region. That said, if I select all areas and only 1 region it shows all areas and the measures only for the regions selected. Trying to see if there is a way to also just show the areas associated with selected regions.

                • 5. Re: Filter that keeps Grand Total as denominator
                  Jeremiah Lobo

                  Hi Joshua,

                  Really loved your solution.Could you explain how this function works as i am not very familiar. I understand only the basic definition as mentioned in Tableau.

                   

                   

                  Thanks

                  • 6. Re: Filter that keeps Grand Total as denominator
                    Marisel Ruiz

                    Hello, Sorry to enter your conversation but I would like to know if anyone knows how to do the opposite to what you request ED KUKEC. I need to display the 50% of the products in this table and the overall column totals.

                         

                    Product Sub-CategorySalesRUNNING_SUM(SUM([Sales]))SUM([Sales]) / TOTAL(SUM([Sales]))RUNNING_SUM(SUM([Sales]) / TOTAL(SUM([Sales])))
                    Office Machines131,381.18131,381.1817.51%17.51%
                    Tables110,544.98241,926.1614.73%32.25%
                    Copiers and Fax94,935.84336,862.0012.65%44.90%
                    Binders and Binder Accessories73,799.70410,661.709.84%54.74%
                    Telephones and Communication65,317.25475,978.948.71%63.44%
                    Storage & Organization64,612.86540,591.808.61%72.06%
                    Office Furnishings47,691.86588,283.666.36%78.41%

                     

                     

                    I put the calculation of tables in filters and samples the smaller ones to .5 but in the general total of column shows the total of 100% not of 50%.