+ Reply to Thread
Results 1 to 10 of 10

Pivot Table - Add Column to Calculate Percent Sold

  1. #1
    Registered User
    Join Date
    10-24-2017
    Location
    Florida
    MS-Off Ver
    Microsoft Professional Plus 2016
    Posts
    9

    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!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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 an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    10-24-2017
    Location
    Florida
    MS-Off Ver
    Microsoft Professional Plus 2016
    Posts
    9

    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. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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
    Last edited by alansidman; 06-08-2021 at 01:40 PM.

  5. #5
    Registered User
    Join Date
    10-24-2017
    Location
    Florida
    MS-Off Ver
    Microsoft Professional Plus 2016
    Posts
    9

    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. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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. #7
    Registered User
    Join Date
    10-24-2017
    Location
    Florida
    MS-Off Ver
    Microsoft Professional Plus 2016
    Posts
    9

    Re: Pivot Table - Add Column to Calculate Percent Sold

    Quote Originally Posted by alansidman View Post
    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. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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. #9
    Registered User
    Join Date
    10-24-2017
    Location
    Florida
    MS-Off Ver
    Microsoft Professional Plus 2016
    Posts
    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.

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Pivot Table - Add Column to Calculate Percent Sold

    For what you are trying to do, I believe that the best means is to use Power Query. You can then filter or sort as needed. There are just some things you cannot do in native excel. Good Luck.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 05-21-2020, 02:43 AM
  2. Replies: 1
    Last Post: 12-18-2018, 04:23 PM
  3. Replies: 7
    Last Post: 04-07-2017, 08:45 AM
  4. Pivot Table Percent Of
    By yawnzzzz in forum Excel General
    Replies: 3
    Last Post: 02-17-2011, 11:07 AM
  5. Pivot Table - Percent
    By yawnzzzz in forum Excel General
    Replies: 2
    Last Post: 06-30-2010, 01:10 PM
  6. Pivot Table - calculate a new column
    By sztob in forum Excel General
    Replies: 3
    Last Post: 03-13-2010, 04:08 PM
  7. [SOLVED] calculate commission $ based on total sold and commission percent
    By blondeindenver in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-05-2005, 10:05 PM

Bookmarks

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