6 Replies Latest reply on Nov 17, 2016 9:33 AM by David Li

    I need to add a "Total" value to a dimension - Please see attached

    Zachary Robinson

      Hi Community,

       

      Tableau noob back again.  I'm sure there is a way to do this (because there almost always is in Tableau), but how to do it exactly is escaping me.  I have the following dashboard:

       

      Tableau.PNG

       

      There is a quick filter and legend that can be used as a filter for dimension "Company."  What I've been asked to do is to add a "Total" to this list so users can see the aggregate amount across all companies, preferably in a separate, single color.  The stacked bars gives users an idea of the total when "All" is selected, but there is no good option for that on the line graph.  I tried playing around with creating a set, but I couldn't get it to work.  I also searched google and community forums for what I'm trying to do, but I'm either not using the correct search terms or not finding the answer, so apologies if this has been answered previously.  I'm still working on my Tableau terminology to be able to use it in conjunction with my google-fu.  Also, I posted here because I assumed this would require a calculated field of some sort, but if this question doesn't belong here please let me know.

       

      What am I missing?  Thanks in advance.

        • 1. Re: I need to add a "Total" value to a dimension - Please see attached
          Matthew Risley

          Zachary,

           

          Welcome!

           

          I'll try and help you out via text- but if it doesn't make sense I can show you via a workbook if you attach one.

           

          You can get around this by making a dual axis chart.

           

          -Go ahead and duplicate the "Company" dimension.

          - using the copied company dimension, Create a group that incorporates all of them into one and call it "All" (or something similar)

          -on your graph, ctrl + drag the DIO measure again onto rows. You now have two graphs

          -on graph 1, drag Company to color

          -on graph 2 drag company (copy) (with the group) to color.

          -Click one of the DIO pills on rows and select "create dual axis"

           

          Let me know if this helps you. (I'm sure there are also other solutions)

          1 of 1 people found this helpful
          • 2. Re: I need to add a "Total" value to a dimension - Please see attached
            David Li

            Hi Zachary! One thing you can do is create a secondary axis that contains the total. However, you should note that when your total is a sum of the constituents, if you want to display it at the same scale as the constituents, it'll blow them out of proportion so they're not very visible.

             

            Let's look at the line graph first. Basically, what you'll want to do is hold down CTRL and click-and-drag the DIO measure's pill (on the rows shelf) to an empty spot right next to itself. This will create a duplicate line graph. Look at the Marks card and you'll notice that there are multiple tabs now. Click on the bottom one and remove Company from the card. This will cause the data to re-aggregate to the total, and you'll end up with a total line. You can format that how you want. Then, merge it onto the same graph by right-clicking the new DIO measure pill and choosing "Dual Axis".

             

            Now, note that there are two Y-axes, but they have different scales. If you want to give them the same scale, you can right-click the rightmost one and choose "Synchronize axes". However, this will cause the total line to blow out the constituent lines. You'll have to decide between using different axis scales and blowing out some of the series.

            1 of 1 people found this helpful
            • 3. Re: I need to add a "Total" value to a dimension - Please see attached
              Matthew Risley

              David,

               

              Well said. My answer is slightly more complicated. I forgot that you could just not use the color shelf instead of copying and grouping the Company Dimension.

               

              Zachary, David's answer is great and for the health of this forum, if our solution works- My vote would be for him to get the "Mark as answered"

              1 of 1 people found this helpful
              • 4. Re: I need to add a "Total" value to a dimension - Please see attached
                Zachary Robinson

                Thanks David and Matthew,

                 

                Is there any way to add this "Total" line to my "Company" filter and legend?

                • 5. Re: I need to add a "Total" value to a dimension - Please see attached
                  Zachary Robinson

                  Also, this only shows the total for companies currently selected.  What they want to be able to do is compare the total overall to individual companies, so the ability to select "total" as well as another company name would be what I'm looking for.

                   

                  Could I create a calculated field called "DIO TOTAL" and add all the DIO values for each company, then create a set and somehow use the set as the filter?  I'm just spit balling here, I have no idea how to do either of those things.  =\

                  • 6. Re: I need to add a "Total" value to a dimension - Please see attached
                    David Li

                    Ah, I see what you're trying to do. In that case, you'll need to use LOD calculations. For instance:

                    { FIXED [Period] : SUM([DIO]) }

                    This will give you the total DIO per period. (Note that you'll have to replace those field names with your real ones.) This will give you the total regardless of what you've selected as long as your filters aren't context or data source filters. Use this field as your second axis instead of copying the existing DIO field.

                     

                    There isn't really a way to add the total to your filter, but it's the same thing as the "(All)" option.

                    3 of 3 people found this helpful