3 Replies Latest reply on Apr 16, 2014 9:30 AM by Patrick A Van Der Hyde

    How do I get row totals to be average and column totals not average and then do a percentage of one average over the other?

    Julia Hennelly

      I have a standard kind of cross tab where I have monthly information (count 1 and count 2)that I want to sum to show an average for a quarter.  That works fine but now I need column totals but these are not averages.

       

      First question is how to do that?

       

      Secondly once I have my quarter average totals I would like to show a percentage of quarter average total of count1%quarter average total of count 2

       

      Can I reference this quarter total fields somehow?

       

      I am attaching a spreadsheet showing the goal; a workbook of showing how far I have got.

       

      Any ideas?

       

      Julia

      support.jpg

        • 1. Re: How do I get row totals to be average and column totals not average and then do a percentage of one average over the other?
          Patrick A Van Der Hyde

          Hello Julia Hennelly,

           

          Thank you for providing a workbook and an idea of what is being attempted.

           

          I have added a workbook to this response with the what I understand is desired from this post. I have utilized a Dashboard to provide the desired outcome within a single view.

           

          As you mentioned in the post, the aggregation choice of "Average" for Totals will impact all Totals for that measure in the view.  In this case, this impacts the Column Totals that should be Totals instead of Averages as I understand it.

           

          To create the Column Totals, I used a Table Calculation to derive the totals per quarter.  This uses  a Window_Sum() function.  The page Diversity Totals and Active Employee Totals shows two views that just have the representative totals for each measure per month.  I created the two sheets in this manner to allow the ideal placement of the values into a dashboard view.

           

          As for the percent of total for the average values per Site Group, I had to calculate these as well using Table Calculations.  In this case, utilizing the Window_Avg() function provides the Average values for each Site group per Quarter.  The calculated field [Diversity Percent of Active Employee] provides the desired result.

           

          For the Window_Sum() calculations, we can utilize the field [Site Group] as the field to Address for aggregation.

          For the Window_Avg() calculations, we can utilize the default Table Across addressing of the calculation.

           

          If you are unfamiliar with Table Calculations, I'd suggest reviewing the online help article related to Addressing and Partitioning and the many other great posts and and examples in the community forums.

           

          I hope this is helpful. 

          • 2. Re: How do I get row totals to be average and column totals not average and then do a percentage of one average over the other?
            Julia Hennelly

            Thank you - this is awesome.  Your solution and the article very helpful. 

             

            The first() function is new to me - it does the trick but would like a little more explanation of it as I can see numerous places where I may use it.

             

            Thanks,

            Julia

            • 3. Re: How do I get row totals to be average and column totals not average and then do a percentage of one average over the other?
              Patrick A Van Der Hyde

              Julia Hennelly

               

              I'm happy to hear that this was helpful.

               

              As for your questions related to First().  First() and Last() are very similar to the Index() function.  First() returns the relative number of rows, columns, or spaces away from the 'first' record that is addressed in the view.  I have a Tableau Public view showing the difference between Index() and First() here.  I have used this view with some of our internal team training and while it relays the same information as the 'addressing and partitioning' page in the online help, I think it may be more helpful to see the functions in play in a view like this.

               

              Table Calculation functions such as Window_Sum() and Window_Avg() are returning results that are aggregations of the aggregate values in the view.  The value is returned however for every mark when what we really want in the view is a single column or row to act as a total row. Using First() within the calculation is very helpful and allows us to compute this value only one time per partition instead of repeating the same calculation over and over.  The first() on the Filter shelf cleans up the view and removes the space that Tableau creates for the empty columns created. 

               

              A much better and longer post (and where I first learned to use first() can be found on Richard Leeke's blog post Clearly and Simply. Even though this post was originally addressing version 6 of Tableau, much of the helpful information here is still relevant today.

               

              Another very helpful function is Size().  The Tableau Workbook Library has a workbook showing Size() in action.

               

              Patrick