How to get percentage of something calculations in Excel Pivot Tables


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

percentage of another value in Excel pivot tables - profit loss statement style calculations - excel pivot tables

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:

sample data - ledger entries - excel pivot table example

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.

add to data model option excel pivot tables

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.

add measure option - excel pivot tables

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())

See all this in action – Video tutorial on “How to calculate percentage of another value in Excel pivot tables”

Check out this short video to understand how to create your percentage of sales measure in Excel.  You can also watch it on Chandoo.org YouTube Channel.

Download percentage calculations pivot table example

Click here to download example workbook for this tip. Right click on the measures and select “Edit” to see the DAX formula.

More advanced pivot table tips

Been making pivot tables for a while and want to graduate to next level? Check out below tips:

Pivot much? What is the coolest pivot you have built

I build pivot tables every day, even on those unfortunate days when I don’t get to work on Excel or Power BI (yes, I build them in my mind on those days, you silly). They are powerful, useful and essential.

What about you? What is the coolest pivot you have built? Please, pretty please, do tell me in the comments. Off you go.

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

2 Responses to “How to get percentage of something calculations in Excel Pivot Tables”

  1. Dipayan deb says:

    Hi,

    I am great fan your work and online teaching, I have created a automatically generated roster in excel but i am not able to add the needed criteria for roster so i need help would you kindly help me to add few criteria according the roster will be generated.
    I will be happy to pay you once you complete the task i will explain the criteria and also send the excel which i have already created if you are happy to help me out in this.

    Regards
    Deb.

  2. Shobi Imran says:

    Thanks Chandoo! Keep up the kind sharing!

Leave a Reply


« »