+ Reply to Thread
Results 1 to 16 of 16

Show per unit in pivot table

  1. #1
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    151

    Show per unit in pivot table

    Hi all

    I have a data table that contains properties, their size in square footage, a budget item, a budget amount, and a per square foot amount. I need to show the correct per square foot numbers in a pivot table and I can't figure out how to do this. For example, if a building has 3,000 SF, and has two budget items of $10,000 and $5,000, I can't find a way to show an accurate per square foot number for each individual line item AND grand totals. The total should show $5 per square foot (i.e. $15,000 divided by 3,000 square feet). However, since it totals the SF of each line item, it shows $15,000 divided by 6,000 square feet, or $2.50 per square foot.


    Any ideas?
    pls click the star if you liked my answer!

  2. #2
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    151

    Re: Show per unit in pivot table

    link to file below

    https://www.dropbox.com/s/q640rsylvb...Test.xlsx?dl=0

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Show per unit in pivot table

    To attach an Excel file to your post,
    • desensitize data
    • remeber that your example should reflect structure and type of data
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post

  4. #4
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    151

    Re: Show per unit in pivot table

    thanks file attached
    Attached Files Attached Files

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Show per unit in pivot table

    Should be $15,000 divided by 3,000 = $5.00
    Should be $32,000 divided by 2,000 = $16.00
    Should be $47,000 divided by 5,000 = $5.00

    why? with subototals everything is OK. You are trying divide subtotals by individual value
    Last edited by sandy666; 02-06-2018 at 06:06 PM.

  6. #6
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    151

    Re: Show per unit in pivot table

    If you look at the pivot table, it is taking $15,000 divided by 6,000 instead of 3,000.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Show per unit in pivot table

    Why instead??? [Sum Amount (Pain+Roof)] / [Sum Feet (Paint+Roof)] = correct

  8. #8
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    151

    Re: Show per unit in pivot table

    No because the property is still only 3,000 square feet. So the goal is to show the per square foot amounts for each line and the total. So paint is $5,000 over 3,000 square feet, or $1.67 per square foot. Roof is $10,000 over 3,000 square feet, or $3.33 per square foot. In total, the work is $15,000 and still is only over 3,000 square. Another way of saying this is $1.67 + $3.33 = $5.00

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Show per unit in pivot table

    So do that in source table then add to the PivotTable

    Subtotal works as I said
    or
    use Calculated Field like this: =Amount/('Property Square Feet'/2) (in this case of course)
    Last edited by sandy666; 02-06-2018 at 06:30 PM.

  10. #10
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    151

    Re: Show per unit in pivot table

    I tried I can't figure it out because I still want to show the full property square feet for each line item and the total. Suggestions?

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Show per unit in pivot table

    check this
    it shows what you want except 47000/5000 is not 5

    or attach example file with result manually created and logical description

  12. #12
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    151

    Re: Show per unit in pivot table

    This doesn't work because your individual lines are now not correct. You have $5,000 and 3,000 square feet = $3.33 per square foot. That should be $1.67. It is wrong because of your divide by 2. See attached for requested desired result.
    Attached Files Attached Files

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Show per unit in pivot table

    You can try (as I said above) add calculated cell(s) to your source range or use PowerPivot (add-in for Ex2010)

  14. #14
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    151

    Re: Show per unit in pivot table

    I tried adding to source range and cannot get it to work, as I said above

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Show per unit in pivot table

    Pivot tables are used in data processing and are found in data visualization programs such as spreadsheets or business intelligence software. Such programs can automatically sort, count, total or average the data stored in one table or spreadsheet, displaying the results in a second table — the pivot table — showing the summarized data

    maybe someone else will give a solution with formula for source range
    Attached Files Attached Files
    Last edited by sandy666; 02-06-2018 at 07:44 PM.

  16. #16
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Show per unit in pivot table

    or this one:
    Attached Files Attached Files
    Last edited by sandy666; 02-07-2018 at 02:32 AM.

+ 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. Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  3. Pivot Report doesn't show current records but they are in pivot table
    By dixiecricket in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 06-16-2015, 11:25 AM
  4. Replies: 1
    Last Post: 03-08-2014, 02:02 AM
  5. [SOLVED] Pivot Table Summary -- Part #'s do not show in Pivot but data is in worksheet
    By maryren in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-19-2013, 10:51 AM
  6. [SOLVED] Multiple Columns in Pivot Table (but don't show in Pivot Chart)
    By Gideon1973 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-23-2012, 10:57 AM
  7. Show minor unit labels for chart
    By Back2Basics in forum Excel General
    Replies: 1
    Last Post: 04-09-2009, 01:26 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