+ Reply to Thread
Results 1 to 8 of 8

Percentages For Each State in Pivot Table

  1. #1
    Registered User
    Join Date
    05-03-2019
    Location
    San Francisco
    MS-Off Ver
    7
    Posts
    28

    Percentages For Each State in Pivot Table

    Hello,

    I have 3 columns of data, all of which can contain duplicates.

    1. State (state abbreviations)
    2. Label One (1 or 0)
    3. Label Two (1 or 0)

    What want is the calculation of sum(Label One)/sum(Label Two) for each state. Any ideas how to accomplish this seemingly basic task?

    Cheers!

  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,810

    Re: Percentages For Each State in Pivot Table

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
    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
    05-03-2019
    Location
    San Francisco
    MS-Off Ver
    7
    Posts
    28

    Re: Percentages For Each State in Pivot Table

    Attached is sample data.
    Attached Files Attached Files

  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,810

    Re: Percentages For Each State in Pivot Table

    Can be done with Power Query

    Here is the Mcode

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    A
    B
    1
    State Division
    2
    CA
    0.75
    3
    NV
    0.5
    4
    OR
    0
    5
    KS
    1
    6
    AK
    0.5
    Sheet: Table1


    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

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Percentages For Each State in Pivot Table

    Or a pivot table with calculated field, as attached.
    Attached Files Attached Files
    Rory

  6. #6
    Registered User
    Join Date
    05-03-2019
    Location
    San Francisco
    MS-Off Ver
    7
    Posts
    28

    Re: Percentages For Each State in Pivot Table

    Thank you!

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

    Re: Percentages For Each State in Pivot Table

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  8. #8
    Registered User
    Join Date
    05-03-2019
    Location
    San Francisco
    MS-Off Ver
    7
    Posts
    28

    Re: Percentages For Each State in Pivot Table

    Done and done. Appreciate your help.

+ 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. Show in a pivot chart only some sub-percentages from pivot table
    By kikonas in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-25-2016, 02:35 PM
  2. Pivot table reverts to an earlier state when clicking refresh
    By bensox in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-05-2014, 01:11 PM
  3. Only getting 100% on pivot table for row percentages
    By darq in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 03-18-2013, 05:30 AM
  4. Programmatically Return a Pivot Table to its Original State
    By fredblogs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-09-2010, 10:57 AM
  5. Pivot Table percentages
    By jswarb001 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-14-2008, 04:28 AM
  6. Pivot Table with Percentages
    By roly in forum Excel General
    Replies: 0
    Last Post: 04-11-2007, 09:40 AM
  7. Replies: 0
    Last Post: 03-16-2006, 07:20 PM

Tags for this Thread

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