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 rowCALCULATE(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:
- Distinct count in Excel Pivot Tables
- Rank totals with in a category in pivot table
- First and last date of a sale using pivot tables
- Generate combinations of two lists quickly
- Excel for accounting – 6 part tutorial
- More on Excel pivot tables & Power Pivot
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.
11 Responses
Ciao Hui,
Collecting Excel tricks under the title “Notable Excel Websites (Non-MVP) Edition” is a brilliant idea…
Thank you in the name of all The FrankensTeam.
On our site there is a box with a picture and text highlighting:
This is a no-MVP site
we think ourselves “bad boys” a bit 🙂
For those who would like to know why our site is a no-MVP site, enough to click on the link:
http://goo.gl/lxDszY
Thank you again!
Thanks a lot
I really enjoyed this (newsletter). I must admit that I rarely read an Excel newsletter (and I subscribe to quite a few) all the way though, but this grabbed my attention and before I realized it, I was engrossed in it. I must also admit that most of this I don’t understand, yet. But, it excites me when I do learn something new in Excel. I can’t wait to see how much of this I can implement into my (constantly-evolving) ‘House Budget’ & ‘Family Medical’ worksheets that I have developed over the past few years! I sure hope to see more of these type of newsletters in the future! Thanks!
Thanks for doing this Hui! I appreciate being included.
I like Tom’s tip a lot. I posted about a tool I wrote to automate this at http://yoursumbuddy.com/tables-edit-query-dialog/
EXCELLENT !
Hui, This post is Superb! More over I have always been a fan of Roberto’s work and have learnt a lot from him.
Here are some of my recent contributions
1. Customising markers in a chart – http://www.goodly.co.in/customize-markers-in-a-chart/
2. Charting Hacks to work faster – http://www.goodly.co.in/5-charting-hacks-to-help-you-work-faster/
3. 7 Date formulas to make life easy – http://www.goodly.co.in/date-formulas-in-excel/
4. Customised scrollbar using VBA – http://www.goodly.co.in/customized-scroll-bar-in-excel/
5. Adding Direct Legends – http://www.goodly.co.in/customized-scroll-bar-in-excel/
Hope everyone enjoys!
I like the Excel Ninja Menus.
1. Select a cell or range then move till the 4-way cross appears. Right-Click and drag the selection to another place in the worksheet then, like a ninja, a menu full of skills and throwing stars pops up allowing me to do all kinds of awesomeness.
2. When you click the fill box on a Date and right click and drag it down, a lot of amazing Date options pop up.
I also brand my Excel to remind myself that I’m awesome. In my personal macro workbook I place the following code.
Private Sub Workbook_Open()
Application.Caption = “SuperKrishna’s Awesomeness”
End Sub
My favorite tip goes along with #17. If you try to copy subtotaled data (and in earlier Excel versions filtered data),when you paste it all the data displays instead of just the summarized data.
To get around this, select your summarized data, click on Find and Select tab and then select Go to Special. Click Visible cells Only and click OK. Now paste and you will see that only the summarized data has been copied.
You can also go CTRL+G and then click the Special icon at the bottom of the dialog box.
What a great idea, Chandoo! I’d love to be included in your next edition:) Perhaps a VBA exclusive version?
@Ryan
I will review this concept about 6 months out from the original post and be sure to keep your site in mind
Hui…
That sounds great, Hui:) I just realized I gave credit to Chandoo for the idea and I should have attributed it to you.
Sorry about that!