How to calculate percentages in a chart or pivot (Dashboards and Worksheets)

Question:
How to calculate percentages in a chart or pivot

ANSWER:
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:

  1. We create a level “Store”, with a grouping key of StoreId.
  2. 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.

  1. Now, in Level 1 (Product), you will be able to
    create a formula for the [Sales by product] = SUM([Sale Amt])
  2. In Level 2 (Store), you can create a formula of the total sales for that store [Sales by Store] = SUM([Sale Amt])
  3. 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.