7 Replies Latest reply on Dec 1, 2016 1:53 PM by Derek Wong

    Creating an aggregate value regardless of dimension

    Derek Wong

      Hello! I've got a quick question that I can't seem to wrap my head around.

       

      In the image above, how do I get a fourth category "ALL CATEGORIES" that sums Sales for all categories? That is, returns the value I would get if I removed category from view, as I have done below.

       

      I've attached a workbook with the above. As a caveat, I don't want to use totals or a new calculated field, because in my real workbook, this would be used as a quick filter to switch the granularity of many other calculations, if that makes sense.

        • 1. Re: Creating an aggregate value regardless of dimension
          Matthew Risley

          Hey Derek,

           

          I'm confused as to why Totals would not be a viable solution for you? Totals do change as you start filtering data.

          Table calculations can also be pretty powerful when you get the "Compute Using" logic correct.

           

          To create a custom category you could also do:

           

          if [Category] = "There is no way in heck this will aggregate anything but total" then "1" ELSE "All Categories" END

           

           

          Best,

          --Matt

          • 2. Re: Creating an aggregate value regardless of dimension
            Stephen Rizzo

            To the best of my knowledge you can't get an additional category without modifying your data structures. There are no rows in your data set with [Category] = "All Categories", and creating a new calculated field will only allow you to reassign existing rows, not create new ones.

             

            If I understand your issue with totals correctly (please correct me if I am wrong), the problem is that you want to use [Category] as a quick filter, except that when you select "All" you want the data to automatically roll up categories as opposed to displaying all categories. If that is a case, there is a workaround. Create a new "lookup" data set that maps the categories displayed in your filter to what you want in included the values. So for your example above, you would have 2 columns and 6 rows:

             

            [Filter] / [Category]

             

            "All Categories"   /   "Furniture"

            "All Categories"   /   "Office Supplies"

            "All Categories"   /   "Technology"

            "Furniture"          /   "Furniture"

            "Office Supplies" /   "Office Supplies"

            "Technology"      /   "Technology"

             

            Then either join (if your data set is small enough) or blend this new data set with your original data set on [Category]. For each row in the original data set, you should now have a duplicate row with [Filter] = "All Categories".

            1 of 1 people found this helpful
            • 3. Re: Creating an aggregate value regardless of dimension
              Derek Wong

              Stephen,

               

              I think you are understanding me correctly--the end result will basically allow the user to see it either way (i.e. either displaying all categories side by side, or rolled up)

               

              I'm weary of using a blend to duplicate all data, as my dataset is quite large already (extracts are already taking ~2 hours without duplicating). I think we're getting closer, but wouldn't like to risk the performance hit.

              • 4. Re: Creating an aggregate value regardless of dimension
                Derek Wong

                Matt,

                 

                Totals won't work for me because my real workbook contains many measures (including table calcs etc). These calcs are distinct from each other and, unless i'm misunderstanding you, a "grand total" across calculations wouldn't yield anything meaningful.

                • 5. Re: Creating an aggregate value regardless of dimension
                  Glenn Kuly

                  Is Analysis -> Totals -> Show Column Grand Totals not sufficient for your purposes?

                   

                  I've tried an approach similar to what Matt mentioned to get counts, but not sure how to do this for a sum of values. I'd like to look at this later if someone else doesn't get to it first.

                   

                  -- Glenn

                  • 6. Re: Creating an aggregate value regardless of dimension
                    Stephen Rizzo

                    Hi Derek,

                     

                    Your performance concerns probably rule out a join (that would double the size of your data set), but blending occurs post-aggregation, so I don't think you would take a performance hit.

                     

                    With that said, if you want to avoid both joining and blending you may need to look into solutions involving multiple tables. Specifically, you could have a dashboard with one table containing data at the [Category] level, and another table with rolled up data displayed directly above / below. Clever use of filters and/or using a helper sheet for the filtering might allow you to replicate the functionality you are looking for.

                    1 of 1 people found this helpful
                    • 7. Re: Creating an aggregate value regardless of dimension
                      Derek Wong

                      I had forgotten that blending was so late in the order of operations! I'll give that a go, thanks.