اكسل

Ever wondered how to get percentage of another value in Excel pivot tables, like this:

In this tip, learn how to create such calculations using Excel pivot tables.

Note: this tip is not compatible with older versions of Excel. If you are using Excel 2007 / 2010 / 2013, then please install free Power Pivot add-in to create measures.

Step 1: Create a data model pivot table from your data

Let’s say you have some ledger entries data like this, in a table named data:

Go to Insert > Pivot Table and create a data model pivot table from this. Make sure you check “Add this data to the data model” option.

Step 2: Create a measure to calculate percentage of another value in Excel pivot tables

Now that we have a data model pivot, you can add measures to calculate various interesting things. Let’s create a measure to calculate the amount as a percentage of sales. To do that, right click on table in fields list and click on “Add Measure…” option.

Write below measure:

=SUM(data[Amount]) / CALCULATE(SUM(data[Amount]) , ALL(data), data[Category]="Sales")

And click ok. Now your measure has been created. Add this to the pivot table values area and you can see other values as % of sales. Cool no?

How does this work?

There are two parts in this calculation.

• • SUM(data[Amount]): gives the total amount for the category / sub-category in the pivot table row
  • CALCULATE(SUM(data[Amount]) , ALL(data), data[Category]="Sales"): calculates the total amount for “Sales” category by first removing filters on the data table { ALL(data) part } and then setting a filter with data[Category]=”Sales”

If all of this sounds like Greek and Latin, then check out my excellent introduction to DAX page first. You will learn how DAX works with simpler examples.

If you want to show the percentages only for non-sales category:

then you can use below measure definition:

=IF(HASONEVALUE(data[Category]),
IF(values(data[Category])<>"Sales",
sum(data[Amount]) / CALCULATE(sum(data[Amount]), all(data), data[Category]="Sales"),
blank()), blank())