4 Replies Latest reply on Apr 4, 2017 10:24 AM by Mark Vee

    Maintaining Denominator via Lookup Calculation -  Unable to Total

    Mark Vee

      Hello All,

       

      Essentially, I only want to view certain product categories on the bar graph but maintain the percentage against the overall category. I figured out via another Tableau post on how to maintain the denominator by using a lookup calc as a filter. The next question is how would I be able calculate the total percentage of the categories that are selected by the filter. So in the below example, I want the graph or even a separate worksheet that I could bring in on a dashboard that states January's total is 61.83% and the total percentage for each respective month. I've also attached a workbook along with a screenshot.

       

        • 1. Re: Maintaining Denominator via Lookup Calculation -  Unable to Total
          Joe Oppelt

          (Note to self:  We're 9.2 in this thread.)

           

          The power of using a table calc as a filter is that it leaves your underlying table intact so that you can get those %-of-total and other table calcs to work on your sheet.

           

          As you are discovering, the weakness of using a table calc as a filter is that it leaves your underlying table intact ...

           

          What you are looking to do is a WINDOW_SUM for each month, but only the sum of whatever is displayed.  You can embed IF logic inside a WINDOW_SUM to say:

           

          WINDOW_SUM( IF <whatever condition I want to limit the sum to> THEN SUM([percent of total sales]) END )

           

          However, the condition here is the table calc itself, and I don't know a way of telling Tableau the selections made by the user in this format.


          What I have done in the attached is to work the table calc filter a different way.  See attached.

           

          Sheet 2 is a copy of your original, only I made a new filter.  I have a row-level calc that sets 1 or 0 based on the values selected by the user for each category.  this way I can see what was actually selected when it comes time to do the WINDOW_SUM.

           

          Now go to Sheet 3.

           

          Here I created a table calc that does what the quick calc did on the first two sheets.  (I need this later on.)  This calc is [percent of total].  I also have a [Total On Sheet] Calc, and here is where I tell it to add only what the user selected.  (Take a look at this calc in the editor to see how I did that.)  That's on the text shelf, and you can see the numbers add up correctly.  Right click on [Total on Sheet] on the text shelf and choose EDIT TABLE CALC.  In here I had to tell this calc to cycle through the dimensions and RESTART each month.  I only want to add up the numbers in each month.  But you'll notice there is a pull-down box at the top of this dialogue box, that is initially set to "Total on sheet".  Pull down.  Select  "Percent of total".  Notice that this is doing TABLE(down), just as it is doing for [Percent of Total] on the ROWS shelf.  I want this calc to evaluate a piece of this one way, and another piece another way.

           

          So, you might be saying, "****, that's a lot of keyclicks for the user."  And yes, it is.  Now take a look at Dashboard 1.

           

          On here I formatted the parameters to have sliders and shaped them so that single clicks change the Y/N values.  It's not as slick as a normal filter, but you still get single keyclicks to turn values on and off.

           

          Of course, you're not doing your actual work in superstore.  I hope your number of dimension values is limited, because you'll need one parameter per value.

          1 of 1 people found this helpful
          • 2. Re: Maintaining Denominator via Lookup Calculation -  Unable to Total
            Mark Vee

            Joe,

             

            This has worked and have used for subsequent workbooks. However, is there way of using dual axis graph to display the total above the bar instead of in the detail while still being able still select and maintain denominator.

            • 3. Re: Maintaining Denominator via Lookup Calculation -  Unable to Total
              Joe Oppelt

              Mark -- Can you post a new thread with that question?  Fresh workbook.  New question...

               

              I'm not available to get to this today, and I'm probably the only one who is seeing an add-on to this old thread.

               

              Thanks.

              • 4. Re: Maintaining Denominator via Lookup Calculation -  Unable to Total
                Mark Vee

                Sure. I'm going to use your fixed example. Thanks for the reply.