Brian, do you have a sample data set that I can look at?
I've attached some sample data to the original message.
Thanks for taking a look.
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.