3 Replies Latest reply on Jul 10, 2013 8:02 AM by Jonathan Drummey

    Aggregation woes

    matthew tagliaferri

      I thought this was a pretty standard aggregation but it's giving me fits.

       

      The attached workbook represents food sales in a ballpark.  To normalize the sales, we want to express them per thousand fans in the ballpark. I have limited the sales to 2 games each in 2012 and 2013, and to only two stands in the park.

       

      The rows for each location are correct.  In stand C101, there were $14,784 sales in 2012, with 56.97 (thousand) fans in the park for those two games, for a SalesPer1000 fans of $259.5.

       

      But the grand total isn't correct.  The second element, ThousandFans, should not be summing.  The 2012 grand total elements should read 36,162 (correct), 56.97 (incorrect), and then 634.75 (incorrect).

       

      Any help would be appreciated.  Thank you.

        • 1. Re: Aggregation woes
          Jonathan Drummey

          Hi Matthew,

           

          Tableau generates the grand total not as a computation (such as a sum) over the displayed marks, but as the original computation at a coarser level of granularity. So in your case the ThousandFans measure is being computed across all the data without LocationDescription to partition it and returning a different result than you'd expect.

           

          In the attached, I changed the measure to use SUM([Attendance])/1000 because that makes clear what the aggregation should be. Beyond that, I used techniques outlined in http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2 to create new calcs that I believe are returning your desired results.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Aggregation woes
            matthew tagliaferri

            This appears to work well.  One question - did you select to make a copy of the LocationDescription dimension because that dimension represented the lowest level in the data?

             

            If I had a more complex data set that included items sold below each location (and item groups, like food/beverage/alcohol), would I instead choose the lowest level Item dimension to copy and include in all the table calcs?

             

            thank you very much!

            Matt Tagliaferri

            • 3. Re: Aggregation woes
              Jonathan Drummey

              What happens in the Grand Total computation for a column is that it effectively removes all dimensions from Rows. In order for the calculations to work properly, they need the LocationDescription available. The LocationDescription (copy) on the Level of Detail makes that available to the Grand Total computation.

               

              If you have more dimensions on Rows, then depending on what you are trying to do in the Grand Total you might need one or more of those dimensions as copies on the Level of Detail, depending on how you want to aggregate within the Grand Total. The post that I linked to has more description of that.

               

              Jonathan