1 2 Previous Next 20 Replies Latest reply on Dec 28, 2011 10:20 AM by Grant Horn

    Subtotals and averages

    Alex Prusakov

      Here is a sample where for counts of Members by Age Group I have Subtotals (per year) but I also need Average Count (per Month). I attached workbook and images. I was able to create averages as one more Measure Value, but it's too much space wasted for single value. This is what I can do  " totalagerage_can.png " vs. what how I want it to look like  " totalagerage_want.png "

      Is this even possible in Tableau?

        • 1. Re: Subtotals and averages
          Joe Mako

          How about the attached?

           

          I duplicated the data, with custom SQL like:

           

           

          SELECT [Sheet1$].[Created Date] AS [Created Date],
          
             [Sheet1$].[Member Age] AS [Member Age],
            [Sheet1$].[Member Gender] AS [Member Gender],
            1 AS [Pane]
          FROM [Sheet1$]
          UNION ALL
          SELECT [Sheet1$].[Created Date] AS [Created Date],
            [Sheet1$].[Member Age] AS [Member Age],
            [Sheet1$].[Member Gender] AS [Member Gender],
            2 AS [Pane]
          FROM [Sheet1$]
          


           

          Notice that I added a field. This enables another subtotal column, and I can hide one, and the display looks as requested. Then with a calculated field like:

           

           

          IF MIN([Month])=MAX([Month])
          
           AND MIN([Pane])=MAX([Pane]) THEN
          SUM(1)
          ELSEIF MIN([Pane])=MAX([Pane]) THEN
          SUM(1)/COUNTD([Month])
          ELSE SUM(1)/2 END
          


           

          That will give you the normal sum under the month headers, and the average for the "Pane" subtotal, and the sum divided by 2 to reverse the effects of the duplication of the data.

          • 2. Re: Subtotals and averages
            Scott Woffenden

            Can you add the average as an additional column rather than an additonal row without modifying the underlying data (i.e., without changing the base table(s), without creating a view/stored procedure, etc.)?

            • 3. Re: Subtotals and averages
              Joe Mako

              Scott, I am not exactly sure what you are asking, but I will try to address your question as I understand it.

               

              What you can do in Tableau is directly related to your data source structure. Different data source structures allow Tableau to create different views.

               

              Tableau can do many great things by itself, and without transforming data. Although, in order to generate the view requested, I transformed the data source to enable Tableau to create the view.

               

              I do not believe it is currently possible to create the view without preparing the data source for Tableau.

              • 4. Re: Subtotals and averages
                Scott Woffenden

                Yeah, I think we are on the same page.  I'm just looking to add a column, using Tableau, called Average.

                 

                So, in other words, we'll have to change the source data in order to add an additional column that's not an "automatic" calculation (for lack of a better term) such as Total, Grand Total, etc.  Is that correct?

                 

                Please let me know if the above doesn't make sense and I'll provide an example of source data and the expected output.

                 

                Thanks very much for your prompt reply.  It's very much appreciated!

                 

                    Scott

                • 5. Re: Subtotals and averages
                  Joe Mako

                  Since any of terms here are overloaded with multiple definitions, it is always best to provide an example of source data and the expected output.

                  • 6. Re: Subtotals and averages
                    Scott Woffenden

                    No problem, Joe.  Here you go.  Unfortunately, due to the whitespace being trimmed when pasting in it's not too pretty.  Please let me know if you have any questions.  Thanks very much.  Scott

                     

                     

                    Sample Input Data (There are 5 rows of data and 2 columns, [Order Date] and [Cost]):

                     

                    [Order Date]      [Cost]

                    01/01/2010        200

                    01/01/2010        300

                    01/01/2010        400

                    02/11/2010        100

                    02/22/2010        500

                     

                     

                    Expected Tableau Output:

                     

                    Jan-2010 Total Cost      Feb-2010 Total Cost      2010 Total Cost      2010 Average Cost

                    -----------------------------      -----------------------------      -----------------------      ---------------------------

                    900                              600                              1500                        300

                     

                     

                    Expected Tableau Output (Calculations):

                     

                    2010 Total Cost = Sum([Cost]) Where Year([Order Date]) = 2010

                     

                        200 + 300 + 400 + 100 + 500 = 1500

                     

                    2010 Average Cost = Sum([Cost]) / # of Records Where Year([Order Date]) = 2010

                     

                        1500 / 5 = 300

                    • 7. Re: Subtotals and averages
                      Joe Mako

                      Scott,

                       

                      I am unable to see how your situation is different from Alex's. Maybe with more data the difference in your situation would be more apparent.

                       

                      Attached is made using your provided data, and the same method as above.

                       

                      Here is the modified calc that I used:

                       

                       

                      IF MIN([Month])=MAX([Month])
                      
                       AND MIN([Pane])=MAX([Pane]) THEN
                      SUM([Cost])
                      ELSEIF MIN([Pane])=MAX([Pane]) THEN
                      SUM([Cost])
                      ELSE AVG([Cost]) END
                      


                      • 8. Re: Subtotals and averages
                        Alex Prusakov

                        Hi Joe,

                         

                        Scotts' issue is exactly the same as mine. And your solution is definitely works. But the problem that this doubles the data. And if users export (view) data they will think it's twice as many rows.

                        Is there any ways of solving this without doubling rows? Is there way use Year twice as Column, but somehow include depth of subtotals to differentiate Totals vs. Averages? Of something using Attr() function  - I tried many things but always came short of actual solution.

                        When roll mouse cursor over the values it shows "Pane:  All" on Totals vs. "Pane:  1" on Averages. But I was unable to use this within calculations.

                        Any advice  -including "this can’t be done" is very appreciated.

                         

                        Thank you, Alex

                        • 9. Re: Subtotals and averages
                          Joe Mako

                          I don't believe Tableau intended there to be two subtotal columns for one pane like I have created here. I am adjusting the visual display by manipulating the underlying data, and I do not know of another way to accomplish what you are asking for. Duplicating the data is what enables this view.

                           

                          As for a user that wants to view the data, you could create another worksheet that only has the non-duplicated data, or a worksheet with a filter that removed one of the "Pane" values, so other global filters work, or use actions to connect the sheets and filter across data sources. I know that is not ideal, but you could have one sheet for viewing, and one sheet for exporting or viewing the underlying data.

                           

                          Another option is to have a single Total/Average column that is controlled by a parameter, so the use can select what is displayed in the single subtotal column.

                          • 10. Re: Subtotals and averages
                            Scott Woffenden

                            Thanks, Joe.

                             

                             

                            As a side-note, I wanted to thank you very much for all your postings.  Without your hard work, great posts and time spent helping everyone I wouldn't be able to complete nearly *any* of what I've been tasked.

                             

                            Thanks so very much.

                             

                                Scott

                            • 11. Re: Subtotals and averages
                              Grant Horn

                              Hi Joe,

                               

                              I have a situation that somewhat follows the issue described in this thread. However, I'd like to know if there's a way to have a row of averages.

                               

                              I've attached a workbook showing total scores for various test types and test companies. I created an average score column, maximum, minimum, and range.

                               

                              Is there a way to have a row that calculates the averages of these columns and lists those averages across the bottom, similar to the grand total?

                               

                              Essentially, show the average of average total scores, average maximum, average minimum, and average range.

                               

                              Thanks,

                               

                              Grant

                              • 12. Re: Subtotals and averages
                                Scott Woffenden

                                Hi Grant,

                                 

                                Just as an FYI, the way we ended up accomplishing the above is to calculate all of the values on the back-end and merely display/format the results in Tableau.  The data behind the scenes ("view data") ends up being the data as it will be displayed on the report.  If you want to display this on a Dashboard with multiple worksheets, that would be your best bet although I'm sure that Joe has a few more tricks up his sleeve than I do :-)

                                 

                                    Scott

                                • 13. Re: Subtotals and averages
                                  Grant Horn

                                  Hi Scott,

                                   

                                  Thank you for the info. I'm not sure if calculating the values on the back-end will work for this situation, but what do I know?!

                                   

                                  I'm just having difficulty figuring out how to calculate the average of each column, as well as having these averages display along the bottom.

                                   

                                  Thanks,

                                   

                                  Grant

                                  • 14. Re: Subtotals and averages
                                    Joe Mako

                                    Are you looking for the attached?

                                    1 2 Previous Next