In an Excel pivot table, you can use the % Running Total calculation, to show the current running total amount, divided by the grand total. For example, see what percentage of total sales come from the top 3 products.
Pivot Table Values
After you add a field to an Excel pivot table’s Values area, you can change the way it shows those values. In most pivot tables that I’ve seen, people just leave the value default settings, to show a quick sum or count of the data.
Don’t get stuck in a pivot table rut! There are two easy ways that you can liven up that data, with just a couple of clicks:
- Summarize Values By: Instead of the default functions (Sum or Count), choose a different function, like Average or Mas
- Show Values As: Choose one of the many custom calculations, like % of Column or Difference From. The video below shows a couple of the other custom calculations – Rand and % Running Total
Be sure to check out all the built-in custom calculations that an Excel pivot table has, before you decide to create your own pivot table calculated field.
Compare Sales with % Running Total
By default, a value field gets the setting “No calculation”, when it’s first added to the pivot table, but it’s easy to change that setting.
The video below shows how to use the % Running Total calculation. With that setting, the value field shows the current running total amount, divided by the field’s grand total.
Set Up % Running Total
In the screen shot below, the Product name is in the pivot table’s Row area, and Sales amount is in the Values area, sorted largest to smallest.
To show a percent running total of sales for each product.
- Add another copy of the sales amount field to the Values area
- Right-click one of the cells in the new column, and click Show Values as
- Click % Running Total in
- From the Base field list, choose Product
- Click the OK button
The pivot table now shows a % running total for each product.
Now, it’s easy to see that the top 3 products accounted for 60% of the total sales
For more on Running Totals, go to this page on my Contextures site: Excel Pivot Table — Running Totals.
Video: Compare Sales with % Running Total
After you add a field to an Excel pivot table’s Values area, you can change the way it shows those values. In most pivot tables, people just leave the default settings, to show a quick sum or count of the data.
Watch this video to see how to choose a custom calculation, to show the sales data in a different way.
Get the Sample Workbook
To get the sample workbook for this video, and to see more examples and videos, go to the Pivot Table Running Total page on my Contextures site.
The zipped Excel file is in xlsx format, and does not contain any macros..
Analyze Top Product Sales with Pivot Table % Running Total
Original source: https://contexturesblog.com/archives/2021/12/09/analyze-top-product-sales-with-pivot-table-running-total/?utm_source=rss&utm_medium=rss&utm_campaign=analyze-top-product-sales-with-pivot-table-running-total