5 Replies Latest reply on May 31, 2016 9:38 AM by Tim Hughes

    How to display Yearly Grand Total while displaying Totals of each location?

    Daniel Chen

      I have yearly totals of each store# by specific month.

       

      To see yearly Grand Total, i have to remove the 'Store #' column.

       

      Is there a way to combine these two into one table?

        • 1. Re: How to display Yearly Grand Total while displaying Totals of each location?
          Tim Hughes

          Hi Daniel,

           

          There are two ways to achieve what you are asking:

           

          1. If you swap the position of your store field with your year field then you can add subtotals (Analysis > Totals > Add all subtotals) for the years.  To remove unwanted subtotals, click the dimension pill on the columns/rows shelf > uncheck Subtotals.

          2. If you want to keep the current layout then you can add store totals as another row using level of detail calculation: {EXCLUDE [Store]: SUM(Sales)}

          Cheers,

          Tim

          • 2. Re: How to display Yearly Grand Total while displaying Totals of each location?
            Daniel Chen

            Thanks, Tim.

            I would need Option #2, but the same Total store sales amount would be showing on column of each row.

            Guess the easier option is to present two tables on a dashboard then use filter to select each individual item?

            • 3. Re: How to display Yearly Grand Total while displaying Totals of each location?
              Tim Hughes

              Hi Daniel,

               

              Sure, you could put both worksheets in a dashboard and then filter to a single item.  But I would encourage you to address your analysis in a more visual way (that is the purpose of Tableau afterall).  For example, you could use a dual axis to show both store and totals together:

              In the above example, the orange and blue marks represent the values from the individual stores and the gray bars represent the overall value.

               

              -Tim

              • 4. Re: How to display Yearly Grand Total while displaying Totals of each location?
                Daniel Chen

                Thank you, Tim.

                For this individual case, i need to use tables instead of visual chart.

                So instead of using the filter in the dashboard, there's no other option except using the Option #2 {EXCLUDE [Store]: SUM(Sales)} to have the same Total store sales amount showing on column of each row?

                • 5. Re: How to display Yearly Grand Total while displaying Totals of each location?
                  Tim Hughes

                  Hi Daniel,

                   

                  Another possibility would be to write a custom SQL connection that builds a totals value within your store field, which gives you something that looks like this:

                  2016-05-31_9-16-07.png

                  If you're using Excel as your data source, make sure to use the legacy connection (Microsoft's JET driver):

                  JET.png

                   

                  The custom SQL to accomplish this is as follows:

                   

                  SELECT

                    [Orders$].[Discount] AS [Discount],

                    [Orders$].[Order Date] AS [Order Date],

                    [Orders$].[Profit] AS [Profit],

                    [Orders$].[Quantity] AS [Quantity],

                    [Orders$].[Region] AS [Store],

                    [Orders$].[Region] AS [Store - Filter],

                    [Orders$].[Row ID] AS [Row ID],

                    [Orders$].[Sales] AS [Sales],

                    [Orders$].[Sub-Category] AS [Sub-Category]

                  FROM [Orders$]

                   

                  UNION ALL

                   

                  SELECT

                    [Orders$].[Discount] AS [Discount],

                    [Orders$].[Order Date] AS [Order Date],

                    [Orders$].[Profit] AS [Profit],

                    [Orders$].[Quantity] AS [Quantity],

                    '' AS [Store],

                    [Orders$].[Region] AS [Store - Filter],

                    [Orders$].[Row ID] AS [Row ID],

                    [Orders$].[Sales] AS [Sales],

                    [Orders$].[Sub-Category] AS [Sub-Category]

                  FROM [Orders$]

                   

                  Instead of writing the SQL from scratch, you can drag your table into the view, and then convert it to a custom SQL connection:

                  Convert.png

                   

                  Otherwise I'm out of ideas .  I've attached the workbook for your reference.

                   

                  Hope this helps!

                   

                  -Tim