3 Replies Latest reply on May 9, 2013 9:11 PM by Jonathan Drummey

    Sum Row Quantity / Count D Table Categories

    brian.comeau

      Hi Experts,

       

      I've been trying to do something for quite some time that really isn't working out well for me. Effectively what I'd like to do is to have multiple levels of aggregation work together on a calculation:

       

      1. We have a total of 3 stores, Store A, Store B, Store C

      2. We sell 4 products in total, Product W, Product X, Product Y, Product Z

      3. We sell them in this manner:

      Store A
      Store B
      Store C
      Product WProduct XProduct X
      Product XProduct YProduct Y
      Product YProduct Z
      Product Z

       

      Given this table, I should expect that if if I ask:

       

      What is the average number of products sold per store of Product Z to be: Sum(Product Z) / 3

      This is despite the fact that Product Z is sold at only 2 stores.

       

      Similarly, the same question for Product W should be: Sum(Product W) / 3

      Despite the fact that Product W is sold at only 1 store.

       

      Hopefully that helps explain the challenge.

       

      Any ideas here?

       

      Thanks,

      Brian

        • 1. Re: Sum Row Quantity / Count D Table Categories
          Neil Sequeira

          Brian, do you have a sample data set that I can look at?

          • 2. Re: Sum Row Quantity / Count D Table Categories
            brian.comeau

            I've attached some sample data to the original message.

             

            Thanks for taking a look.

            • 3. Re: Sum Row Quantity / Count D Table Categories
              Jonathan Drummey

              You didn't specificy what made for a # of products sold, so in the attached I just used # of Records for that and TOTAL(SUM([Number of Records])) with a Compute Using on Size generate the # of products sold.There are three worksheets that cover a variety of techniques to count stores:

               

              - crosstab uses the SIZE() calculation. With this particular pill layout domain completion is triggered and SIZE() with a Compute Using of Table Across (or on Store) and returns accurate results (many duplicates of the accurate results).

               

              - single column gets uses the SIZE() calculation with an advanced Compute Using of Store, Product Code with At the Level set to Store. This gets around the domain completion issue by how At the Level works to partition on position. However, when the dimension pill used for At the Level is on the Marks Card we get unwanted padding, so there's a table calc filter on the Filters Shelf. For this one, the Avg # of Products Sold uses a nested table calculatio where the Size calc has the aforementioned Compute Using settings and the TOTAL(SUM(Number of Records)) part has a Compute Using of Sales.

               

              - The extract worksheet uses an extract data source to make the COUNTD() function available. I used the formula SUM([Number of Records])/TOTAL(COUNTD([Store])) to generate the averages. TOTAL() is a special table calc that computes the inner aggregate at the level of detail specified by the Compute Using, this way with a compute using of Product Code it returns 51.

               

              Jonathan