+ Reply to Thread
Results 1 to 8 of 8

How to show Percentage of all data in PivotTable (Not % of summed values)

  1. #1
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    How to show Percentage of all data in PivotTable (Not % of summed values)

    Hi Forum,

    Long time no speak!

    I have a problem where I am trying to filter the top 80% of results in a PivotTable and have the SUM of the Capital Expenditure show as a by-product.

    What i mean by this is that I am not trying to filter the data so that it shows the top ranking 80% of the data, as this filter takes into account the highest capital expenditure fields and takes the top 80% of those.

    What i want to achieve is a filter where it shows the top 80% of all results. i.e. 80% out of 100% of all the Capital Expenditure cases are shown and the 20% at the top (top as in top of the page, not top results by value) are filtered out.

    E.g:
    Date Title Capital
    2019 AXE 200
    2019 CUBE 150
    2019 TREX 3750
    2019 TYRANT 5900
    2019 PIG 301

    The data should filter to top 80% of all cases as:

    Date Title Capital
    2019 AXE 200
    2019 PIG 301
    2019 TREX 3750
    2019 TYRANT 5900

    Is this does not make sense I will create a mock-up sheet of what I am trying to achieve/convey! Thanks in advance!
    Last edited by JulianS96; 08-19-2020 at 08:12 AM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: How to show Percentage of all data in PivotTable (Not % of summed values)

    Is this does not make sense I will create a mock-up sheet of what I am trying to achieve/convey! Thanks in advance!
    That is always a good idea.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    Re: How to show Percentage of all data in PivotTable (Not % of summed values)

    Attached My data!

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: How to show Percentage of all data in PivotTable (Not % of summed values)

    And what should be the result in your file.

    Since I expect you want 4.805,10 as result and you have that result already in your file.

  5. #5
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    Re: How to show Percentage of all data in PivotTable (Not % of summed values)

    Ah sorry my expected result is for the PivotTable to match what is shown on the right table.
    The table on the Right isn't even a PivotTable. I just copied the data from the PivotTable and deleted some rows manually to get the result I wanted.
    How can i achieve this with the PivotTable?

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: How to show Percentage of all data in PivotTable (Not % of summed values)

    Sort the data.

    E1 = 80%

    E3 =IF(COUNTIF($B$3:$B3,$B3)<=($E$1*COUNTIF($B$3:$B$27,$B3)),"YES","")

    After that a pivot table.

    See the attached file.
    Last edited by oeldere; 08-19-2020 at 12:22 PM.

  7. #7
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    Re: How to show Percentage of all data in PivotTable (Not % of summed values)

    Hey oeldere!

    Thanks for that rather easy, elegant solution.
    I was hoping it could be done somewhere within the pivot table but this is a good idea. Means I only have to change one value and then update it.

    Would there be any way to do it in the PivotTable's current format without modifying the data? If not then I know for next time to create a helper column
    Last edited by JulianS96; 08-20-2020 at 04:05 AM.

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: How to show Percentage of all data in PivotTable (Not % of summed values)

    I searched for a solution within the pivot table, but was not able to find it.

    So, I come with an alternative to solve the question.


    Glad I could help.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. After updating PivotTable Source data with Macro, slicer doesn't show PivotTable
    By mrdouglaswee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2018, 02:10 AM
  2. [SOLVED] Formula to show empty cell if there is no number in the cells to be summed.
    By headley4ever in forum Excel General
    Replies: 4
    Last Post: 07-01-2016, 03:43 PM
  3. Replies: 2
    Last Post: 03-10-2014, 09:57 PM
  4. Select which summed value fields to show or hide on PivotChart
    By PixlPgsis in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-21-2013, 03:46 PM
  5. Show data in text as percentage
    By Rabinow81 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-11-2012, 06:17 AM
  6. Replies: 3
    Last Post: 02-15-2011, 06:35 AM
  7. Show difference between values as a percentage
    By gkitf16 in forum Excel General
    Replies: 2
    Last Post: 08-31-2010, 12:12 AM

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