2 Replies Latest reply on Feb 1, 2013 11:48 AM by Patricia Santillan

    Grand total of averages

    Patricia Santillan
    Patricia Santillan Feb 1, 2013 8:45 AM

      I have a data of bus ridership grouped by division and a few of those divisions are grouped together.  For instance Downers Grove, Highland Park and Village of Niles are grouped into a Municipal group.  Each of those divisions have varies numbers of bus routes that serve that area.  I need to get average daily ridership by division so I summed the ridership and divided it by count distinct on date so that it would average the ridership by the number of days that division operated. 

                                                             sum([Ridership])/countd([DATE])

       

      No problem - although I probably could have done this in a more efficient way.  The average for each division comes out perfect.  However, I also need to add a grand total to this group.  The total adds Downers Grove (299), Highland Park (522) and Village of Niles (890) and comes up with 1695.  Those three numbers added together equal 1711, not 1695.  Tableau is using the same calculation above on the total so its taking the sum of the ridership from these three divisions divided by number of distinct days of the month.  The problem is that 2 of those division worked 20 days and one worked 19.  The grand total is division ridership sum divided by 20 which is the total number of days for the group.  .  I just want to get a total of 299+522+890.  How do I do this? 

        • 1. Re: Grand total of averages
          Chris Tsui
          Chris Tsui Feb 1, 2013 9:14 AM (in response to Patricia Santillan)

          Hi Patricia,

           

          I believe Tableau is doing what it is epxted to do in the Grand Total column which is taking the formula used and performing it at a higher level of detail.

           

          I ran into a similar issue before and I found that Johnathan Drummey's Article on Customizing Grand Totals (http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/) to be extremely helpful.

           

          From that article I believe what you'll need to to is to modify your formula to look similarily to:

          IF MIN([Division]) != MAX([Division]) THEN

              //Grand Total result

              SUM([Ridership])

          ELSE

              //detail row result

              sum([Ridership])/countd([DATE])

          END

           

          Because the Grand totals ignore the Division pill when aggregating, the if statement would be false when [Divisions] are present (in the details area).

           

          It took me a while to wrap my head around it, but I think this is what you're looking for. 

           

          And I will always caveat with..... the Experts though may have a better or more correct explanation

          • 2. Re: Grand total of averages
            Patricia Santillan
            Patricia Santillan Feb 1, 2013 11:48 AM (in response to Chris Tsui)

            Once I figured out the reason for the difference I understand why I just needed a different solution for this instance.  The article was super helpful but over my head in the explanation part.  I'm having a hard time wrapping my head around it but if I simply follow the instructions blindly I can get to the point were I get multiple values - the right values I might add - on top of each other so it appears in bold.  I can't get the min/max formula to work for me, either Johnathan's formula or your suggestion, but I think I'm ok with that.  I probably would have bolded it anyway. I am going to have my co-worker take a look at it.  She is way more skilled at this.