You can build in-table summaries of the results in Analytics. For example, if you want to summarize results by the subcategory only, or use the top-level and secondary categories together. You can build calculations into Analytics to build sums and gather these together in a customized way that's useful for you.

If you start with a First Subcategory report that you customized earlier, where you start with the complete category and remove everything after the subcategory, you could prepare data to gather totals by First Subcategory. To do this, you'll need Calculations because you can't use a calculated field as a dimension.

Sort by First Subcategory

Sort by the first category and identify the last instance of every First Subcategory by clicking Item Category.

Determining the last instance of each First Subcategory

Determine which is the last instance of each First Subcategory.

1. Start by using a simple Offset function to look at the First Subcategory field.

2. Add a comma and 1 (,1) to make the calculation refer to one row down.

   

    

    

3. Click Save and run the calculation. In the report to the right of each First Subcategory, the First Subcategory Below displays.

   

4. Then, to see if they are the same, use one more simple calculation:

   = If the values are equal, Analytics will return a Yes, if not, Analytics will return a No.
   So start with two sets of brackets with an equal sign in the middle.

   

5. In the first set of brackets, search and enter the First Subcategory calculation.

   

6. Copy the offset function, referring to the row below, and then paste it into the second set of brackets.

   

7. Save and run the calculation. You'll get a Yes/No calculation where the No identifies the final instance of every First Subcategory.

   

8. Add a title and description, and click Save As New.

   

Determining how many rows are the same

Now that you know the last instance of each First Subcategory. Next, you'll also need a count of each one. A way to start this involves the Match function, which returns the first row on which a value occurs.

1. In a new calculation, enter match(). In the brackets, search and enter the first subcategory.

   

2. In the brackets, after the first subcategory, add a comma, search for and enter the first subcategory.

   

3. Save, and on your report, you'll get a Calculation repeating the first row on which each First Subcategory occurred.

4. Next, start a Calculation where you return the row().

   

5. Then include a subtraction symbol and another set of brackets.

   

6. In the second set of brackets, copy and paste the matched reference.

   

   

7. Click Save As New and run the calculation. A 0 displays for each First instance of a First Subcategory, and a subsequent count on each row of the subcategory.

   

   

Creating a first and last reference point

For the Offset List function you'll need to uaw three arguments:

• The values (which will be our Sales Line totals)
• The starting point for counting (or, how far back you want to start)
• The endpoint for counting (or, how many rows you want to include)

The values are already on the table. The negative row count for each last instance of a First Subcategory, so this becomes a simple IF statement.
If you're looking at the last instance of the First Subcategory, show the negative count, if not,return a 0

1. Start with brackets.
   

2. Then copy the calculation, looking at whether the reference is the same or not.

   

3. Paste the calculation, and copy and paste the second function, looking at the count.

   

   

4. Click Save And Run. For each No, a negative total should display. For each Yes, a zero should display.

   

5. Now, you need to define the endpoint. So this will need to be the row count plus one, because each count starts with zero. You can copy and paste the same basic function above.

   

6. Substitute the negative sign with a positive 1.

   

   

7. Click Save And Run. You'll get another calculation with the opposite inverse number plus one.

   

   

Gathering the results

Now you can start gathering the results.

1. Start with the offset_list function with brackets

   

2. Next, in the brackets, search and add the value you're looking at, for example, the sales total.

   

3. Include your Back calculation

   

4. End with the Forward calculation.

   

5. Your final calculation should look like the following image:

   

6. Save and run the calculation. In your report, in the right column, you'll see a list of all the Sale Line totals. Each list should only be displayed on the last instance of each First Subcategory, and should reference only its respective totals.

   

7. Lastly, create a sum of that group total.

   

   

8. Click Save And Run. In the report, where applicable, you'll get one total per First Subcategory.

   

   

Cleaning up calculations

There are some supportive calculations here that you shouldn't need for this report. You'll use and need to keep:

• First Subcategory
• Back
• Forward
• Group Total
• Same (at least once or twice more).

You can remove everything else.

Your edited report should be oprganized better.

Looking for the last results

Now you can use the report to gather meaningful data.

1. To start, ask Analytics to return a 1 for every instance where "Same" is No, and return 0 otherwise. So start the calculation with brackets.

   

2. Then, copy and paste the calculation for checking whether the First Subcategory is the same as the one below.

   

3. Save and view the report, and you should see a 0 for each Yes, and a 1 for each No.

   

4. Next, to get these to rise in sequence, use the running_total function to return that number. Copy and paste this.

   

5. Copy and paste

   

   

6. Click Save As New and view the report

   

7. Next, to match the row of the report to the rising number enter: match(row(),)

   

8. To make the row of the report match to the rising number copy and paste the calcultion from step 5.

   

9. The final calculation should look like this:

   

10. Save and run the report. Each row will return where the next First Subcategory can be found.

    

    

Gathering labels and totals

Now, to complete the preparation of the data, you're going to use the Index function:

1. Enter index()

   

2. Enter the first subcategory label that you created.

   

3. Then, from them, gather the indexed number.

   

4. Copy and paste into your calculation.

   

5. Click Save And Run.

   

6. This creates a singular entry for all the First Subcategories. Finally, do the same for the grouped totals

   

7. Click Save And Run. This isn't the final version. Complete the next procedure to clean this report.

   

Hiding additional columns

1. Open the Table Visualization to view all the starter data.

   

2. Only two of these columns are currently relevant. Hide the additional columns by clicking the Settings (Gear icon) in the column > Hide from Visualization.

   

3. Keep the labels.

   

4. Keep the grouped totals.

   

5. Now, when you view the visualization, you'll have a cleaner list of all the summary totals by first subcategory.