Search

# How to get percentage of something calculations in Excel Pivot Tables

Share

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

### 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.

### 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.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]

Ever wanted to automatically format values in thousands, millions or billions in Excel? In this article, let me show you two powerful techniques to do just that.

## Related Tips

Keyboard Shortcuts

Learn Excel

### How to make a pivot table when you have data in multiple sheets [Tutorial]

Charts and Graphs

Power Pivot

Learn Excel

### These Pivot Table tricks massively save your time

Cool Infographics & Data Visualizations

### 11 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!

3. Hiking tour Damavand says:

Pretty! This was an xtremely wonderful post. Many thanks foor supplying these
details.

4. Imran says:

How to get percentage of something calculations in Excel Pivot Tables

HI Chandoo
Hope you are doing well. I have applied your Dax formula on my excel sheet but I got data in different column as departments. And I need excel to calculate the percentage on the total of the each cost center but this one instead has taken the grand total of all the string under sales.

May I please ask for some help on this percentage calculations on different columns (For Each Department). Please share the formula. Thanks

Regards
Imran

5. Swordfish76 says:

Hi Chandoo,
this post was extremely helpful! Thanks for it. I was doing this with using two pivots with slicers until now, but it wasn't that comfortable. Using calculated field in pivot table as you shown in this post was much better.

6. Ramona says:

Hello,

I have the same problem as Imran
On columns i have months and i have different cost centers and I need excel to calculate the percentage of costs per each month, on the total sales of each cost center.

Thank You!

• Chandoo says:

Hi Ramona...

You can use this measure to get the % of cost center sales output.

=sum(Range[VALUE])/ calculate(sum(Range[VALUE]),all(Range[LinieBuget]), Range[TYPE]="SALES")

• Naum says:

Hello,
I have similar problem,
I have Profit & Loss per years ( years are columns) and i need to (measure) calculate Percent of every row from SALES, BUT PER YEAR?
Can you help me?

7. Naum says:

Hello,
I have similar problem,
I have Profit & Loss per years ( years are columns) and i need to (measure) calculate Percent of every row from SALES, BUT PER YEAR?
Can you help me?

8. Ohio Fan says:

Hello,
Big fan of your work! Can you calculate a percentage of
another value in Excel pivot tables without using power pivot? Is there a work around? I have some users still using Excel 2010 and 2013(we can’t install power pivot plug-in)

9. Jens Stencel says:

Hi Chandoo

I use this formular to calulate percentage of the revenue in my income statement ( I have seen it in your you tube channel:-))

=Sum('LEDTRANS 1'[AMOUNTMST])/Calculate(sum('LEDTRANS 1'[AMOUNTMST]);all('LEDTRANS 1');'LEDTRANS 1'[Type]="Revenue")

It calculates the correct percentage for all the data but if I chose a month in the pivot table the result is not correct, hope you can help?

Best regards

Jens

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.