How to calculate percentages in a chart or pivot
Currently, percentages must be calculated in a worksheet, then added to a chart / pivot, as follows.
Let’s say that you need to calculate the percentage of $ sales for each product, in relation to all products sales, in every store.
In the worksheet:
- We create a level “Store”, with a grouping key of StoreId.
- We create a level Product, with a grouping key of [ProductId]. That will become our level 1, with the Store now becoming Level 2.
Level 2 = Store while Level 1 = Product means, that Store is a parent of Product, and we will be able to look at products within a given store.
- Now, in Level 1 (Product), you will be able to
create a formula for the [Sales by product] = SUM([Sale Amt])
- In Level 2 (Store), you can create a formula of the total sales for that store [Sales by Store] = SUM([Sale Amt])
- In Level 1, create a percentage formula [Percent of Sales by Product] = [Sales by product] / [Sales by Store]
Now you can put that formula into a chart, and it will work correctly.