7 Replies Latest reply on Nov 29, 2012 10:22 AM by Jonathan Drummey

    Toggling Between % Total and $

    Robert Rama

      I have a chart with departments across the top and expense accounts along the side.  I'd like the user to be able to toggle between $ amounts and % of column as well as % of row.

       

      I also have Grand Totals along the bottom (total for each department) and along the right side (total for each expense category).  Of course these would be meaningless in a % of total view (they would all be 100%).  Assuming there was a way to toggle between % and $, then would there be a way to hide these Grand Totals if % of total were used?

       

      Sorry but one last question, is there a way to move the Grand Total location?  Specifically, rather than having the Grand Total along the right side as the last column, I'd like to have it as the first column.

       

      Thanks for any input.

        • 1. Re: Toggling Between % Total and $
          Shawn Wallwork

          Hi Robert,

           

          I'll take these in reverse order:

          • No there currently isn't a way to change where Grand Totals are displayed. You can vote this Idea up here:

                    http://community.tableau.com/ideas/1320 [Note: this is going to produce a broken link, you'll need to copy and paste it into your browser for it to work.]

          • If turning the Grand Totals off/on is important then you'll want to create two separate worksheet, one with $ and one with % (and each with Grand Totals or not) and then use Dashboard Actions to hide/reveal them.
          • If you can live with Grand Totals showing 100%, then you can do the $ to % switch on the same worksheet using a calculated field, something like the following:

           

          IF [Which Values]='Dollars' THEN RAWSQL_STR("FORMAT (%1, 'currency')",([DollarValues]))

          ELSE RAWSQL_STR("FORMAT (%1, 'percent')",([PercentValues]/100))

          END

           

          [You may or may not need the /100 depending on the form of your underlying data.] When you put this on the Label shelf, you can then use the parameter [Which Values] to switch the label formatting. You'll need to create this while using a live connection (not an extract) so the RAWSQL functions are available to you. But as Richard recently taught me, after you've set up the RAWSQL statements you can then extract the data and these functions will still work. You won't be able to edit them without removing the extract first, but you can use them with the faster Tableau data engine.

           

          Let me know if any part of this didn't make sense to you. Also, if you can post a sample packaged workbook I'll be happy to set it up for you.

           

          --Shawn

          • 2. Re: Toggling Between % Total and $
            Jonathan Drummey

            You said the magic words, Shawn. "No there currently isn't a way..." :)

             

            I'll post a working single worksheet solution as soon as I get some decent WiFi.

            • 3. Re: Toggling Between % Total and $
              Elvis Has Left The Building

              Dang! I forgot about your work-around. Slap my face!

               

              --Shawn

              • 4. Re: Toggling Between % Total and $
                Robert Rama

                Thanks Shawn.  I'm a newbie at Tableau so the basics is all I know.  So please bear with me. I did proceed to make two separate views with the % total view lacking a total row. I need to read up on dashboard actions and see how I can allow users to switch between the two.  Thank you for pointing me in that direction.

                 

                Jonathan thanks for the reply and I look forward to your workaround on the grand total issue.

                • 5. Re: Toggling Between % Total and $
                  Jonathan Drummey

                  This was fun! I used the Custom SQL techniques from http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-3/ and Shawn's bit of RAWSQL formatting magic, along with a parameter and calculated fields for the measure and the filter.

                   

                  The tricky bit was getting the data ready. I first thought I could just follow the recipe to get the left hand grand total, and use Tableau Grand Totals for the bottom, but that doesn't work because we don't have a way to turn on/off Grand Totals on the fly. So instead of doing a single UNION to duplicate the data (once for Grand Totals and once for the data), I needed to do three UNIONs to have 4 copies of the data:

                   

                  1. for the raw data

                  2. for the left-hand Grand Total column for the dimension of interest

                  3. for the bottom Grand Total row for the dimension of interest

                  4. for the Grand Total of the Grand Totals

                   

                  You can see the raw results in the Custom SQL worksheet in the attached. This is necessary because even though Tableau will do domain completion to draw a crosstab, it won't fill those cells with data and getting them to show data can be tricky, it requires some table calculation gymnastics that are better avoided if possible.

                   

                  Next, we need to revise the headers for the chosen dimensions (Category and Container from the Superstore Sales data), and here I just used the instructions from my blog post. That's the Headers in Place worksheet.

                   

                  Now it was time to get the filter to work properly, to get rid of the Grand Total column & row based on a parameter selection. That was a fairly straightforward calc, the result is in the Filters in Place worksheet.

                   

                  Finally, the last bit is the calculation that will return different values based on the selected parameter. Based on the requirements, we need a couple of different nested table calculations in order to support % Across and % Down. I don't know of any way to change the Compute Using on the fly, but it is possible to change the calc that is returned via parameter. Another issue in this is that the wonderful RAWSQL formatting isn't available for table calcs, so we have to do some string manipulation to get the values right. See the $ Sales or % of Total Sales worksheet for the details.

                   

                  Now for the real question: Is this solution more workable than alternatives? It does require having 4 copies of your data, which could be larget or require more work to pre-aggregate it. Alternatively, you could get the totals on the left and bottom using the Custom SQL solution with only one duplication of data, then have a couple of worksheets on a dashboard and swap them out via parameter, but that would run into more complications if you want to do any user-entered filtering across worksheets (though that goes away in Tableau v8).