# Pivot Table - Add Column to Calculate Percent Sold

1. ## Pivot Table - Add Column to Calculate Percent Sold

I have a spreadsheet with all of my companies accounts from the past year, both sold and unsold organized by zip code.
The main purpose of what I'm using this for is to help better direct our marketing efforts. I want to see the number of sold accounts, unsold accounts, total accounts and percentage sold in each zip code. The actual value of the sale is not important for this.

Example: (Did my best to make up it line up)

Zip code Sold Unsold Total %Sold

75022 ___4 ___ 8 ___ 12 _ 33.3%
75028 ___7 ___24 ___31 _ 22.5%

When I create my pivot table, I'm able to see exactly the above, except for the last column. How can I create a column to show % Sold?

I'm assuming that my data is just set up incorrectly but I've been trying all day and cannot figure out how to organize it properly so that when I create the pivot table, I'm able to select the options I need.

I've attached a small sample of the data and the pivot table I created. Any help is very much appreciated!

2. ## Re: Pivot Table - Add Column to Calculate Percent Sold

Using Power Query, I replicated your expected results.

``Please Login or Register  to view this content.``
Excel 2016 (Windows) 32 bit
G
H
I
J
2
Mailing Zip Sold Unsold %Sold
3
75028
0
1
0
4
75022
4
8
0.333333333
5
75027
0
1
0
6
75028
7
23
0.233333333
7
75050
0
4
0
8
75051
0
2
0
9
75052
0
4
0
10
75049
0
1
0
11
75050
3
27
0.1
12
75051
2
24
0.076923077
13
75052
7
44
0.137254902
14
75038
2
10
0.166666667
 Sheet: Data

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for a video which demonstrates how to use Power Query code provided.

3. ## Re: Pivot Table - Add Column to Calculate Percent Sold

Hi Alan,
Thank you for the reply! When I download what you created with Power Query, I am unable to access any of the other data as I would be able to in a Pivot Table.
For example, in the pivot table in my example, I am able to double click on the total number of accounts on one of the lines and it will open a new workbook showing me all the accounts for that zip code along with information in other hidden columns.
Is there not a way to accomplish what I'm asking in pivot tables? I've watched youtube videos on it but they're all geared towards averaging sales dollars which isn't important for me. Because mine is more of a "true/false" (sold/unsold) argument, I cannot seem to replicate what they're teaching.

4. ## Re: Pivot Table - Add Column to Calculate Percent Sold

You might be able to accomplish it with Power Pivot. You will need to do some research on how to create the correct measure in DAX. I am not advanced enough in Power Pivot to assist on that.

EDIT: Look at this link. You may need to restructure your original data howerve.

https://www.myonlinetraininghub.com/...e-column-total

5. ## Re: Pivot Table - Add Column to Calculate Percent Sold

Thanks for trying. I had watched someone do something similar using DAX but it was to calculate averages of sales. I can't figure out how to replicate it for what I need

6. ## Re: Pivot Table - Add Column to Calculate Percent Sold

I think that I have found a workaround. I wrote a VBA to calculate based upon your original Pivot Table supplied

``Please Login or Register  to view this content.``
A pretty straight forward calculation when done this way. Sometimes we try to over manage the situation when KISS is the way to go.

7. ## Re: Pivot Table - Add Column to Calculate Percent Sold

Originally Posted by alansidman
I think that I have found a workaround. I wrote a VBA to calculate based upon your original Pivot Table supplied

``Please Login or Register  to view this content.``
A pretty straight forward calculation when done this way. Sometimes we try to over manage the situation when KISS is the way to go.
Excuse my ignorance but I have no idea what to do with that

8. ## Re: Pivot Table - Add Column to Calculate Percent Sold

How to install your new code
• Copy the Excel VBA code
• Select the workbook in which you want to store the Excel VBA code
• Press Alt+F11 to open the Visual Basic Editor
• Choose Insert > Module
• Edit > Paste the macro into the module that appeared
• Close the VBEditor
• Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
• Press Alt-F8 to open the macro list
• Select a macro in the list
• Click the Run button

9. ## Re: Pivot Table - Add Column to Calculate Percent Sold

I really appreciate the ongoing help Alan. This worked for helping calculate the percentage of sales in a separate column to the side of my pivot table which is helpful.
I'm still looking for a way to have everything in the pivot though as it's the only way for me to manipulate the data. If I wanted to sort the data by zip code to show where we're getting the best return (# of sales vs total accounts) I'm still limited with your method, as I'm not able to filter the data in the column created by the VBA code.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1