3 Replies Latest reply on Aug 19, 2018 8:27 AM by Bryan Pon

    Understanding how additional dimensions change an aggregate calculation

    Bryan Pon

      So I need some help conceptually understanding how aggregations work, and how the calculation changes when I add new dimensions. I think I'm just missing the underlying math.

       

      Applied to Superstore data:

       

      For example, if I want to show average sales per customer, I can easily do that. But if I want to then show the breakdown of those sales by product category, dragging the "category" dimension onto the view changes the underlying calculation in a way such that the subtotals of the three product categories add up to more than the total of sales; e.g. if I do a bar chart and then add the "Category" dimension to Color button to create a stacked bar, the stacked bar is now higher.

       

      I know this has to do with how the average is being calculated, and maybe it's not possible to show that breakdown given that I'm trying to show an average. But I feel like this should be possible with an LOD calculation, I just can't figure it out.

       

       

      Thanks!

      Bryan

        • 1. Re: Understanding how additional dimensions change an aggregate calculation
          Jim Dehner

          Hi Bryan

          Interesting questions - glad you asked

           

          it is sometimes easier to see what is going on in a text table

           

          the first column is just average sales and uses AVG to total it will return the 229.9 which is the save average as you would get using either the second or the third column

           

          it is the average of all the individual sales figures

           

          the fourth column is

           

           

          The way you would read an LOD in words would be "for each combination of the dimensions that precede the colon - aggregate based on what follows the colon"  --

          the LOD is not an aggregate in itself so when you bring it to the viz it is aggregated again

           

          so the fixed avg has no dimension before the colon - so it will give you the average of all the  sales in the d/b it is unaffected by the year filter

           

          the exclude LOD - will take into account the year filter but not the category and will give you the average for the individual sales in 2018

           

           

          the Include LOD is going to use the date filter and only look at 2018 and will compute the average of each category separately - when I brought it to the viz I use average for the total so it divides the sum of the individual averages by 3  (you could use sum and it would just be the total

           

          Note also that the simple average and the exclude average return the same value - the average of all the individual sales in 2018

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          2 of 2 people found this helpful
          • 2. Re: Understanding how additional dimensions change an aggregate calculation
            Deepak Rai

            Hi Bryan,

            Please See attached, It all depends at Which Level you want to see the aggregations and Data Changes accordingly.

            Thanks

            Deepak

             

            1 of 1 people found this helpful
            • 3. Re: Understanding how additional dimensions change an aggregate calculation
              Bryan Pon

              Thanks Jim, Deepak, both really helpful explanations and examples for working with LOD expressions.

               

              I'm realizing the nut of my question only applies to aggregation with averages, and again I think it's just conceptually around how I can calculate and represent those.

               

              Attached is a workbook showing average total sales per customer, by region. Then the next tab tries to apply the Category dimension, but this of course the re-calculates the average sales on a per category basis, so the overall averages per region increase. I want to keep the totals of the first tab, but find a way to show that Category breakdown.

               

              Regards,
              Bryan