+ Reply to Thread
Results 1 to 4 of 4

Pivot Construction

  1. #1
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Pivot Construction

    Hi All

    I dont think this is possible, and give up even trying now but.......

    Can i calculate rows in a pivot table? and also keep the format of a cell locked to the heading of the row?

    Basically im looking at quantity (numeric), sales (£), gross profit (£) and gross profit (%)

    As we all know pivots sum the total of the data it is looking at, which is fine for the first 3, but for the % i need it to do the calculation gross profit divided by sales. I can get this in a calculated field, but my pivot has these "measures" in the rows, not the columns

    Period (All)
    Hemisphere (All)

    Measure Sum of Actual Sum of Last Year
    Actual Sold Qty 4,639,821 6,588,769
    Net Sales Amount £65,302,872 £84,059,859
    GROSS MARGIN £4,701,211 5584394.804
    GM% -139.2% -136.8%
    Grand Total

    So above the GM% is a sum of my data, but i want it to calculate the above mentioned.

    Is this possible? and also if say this ranges from cell a5-d9, if the pivot is refreshed and new rows are inserted how can i make sure certain rows are numeric, currency or percentage?

    As i said i have given up so this is my last shot, any help, or even someone telling me it cant be done would be great

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Pivot Construction

    without seeing the raw data I would try something like this

    in your PT add another value - it will be your gross profit field - now you have 2
    in the new data field right click and choose "show values as" and there are a bunch of choices for %'s....HTH....
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: Pivot Construction

    Hi

    Thanks for that, i had a look and still couldnt figure it out, i ended up reconstructing all of my data and put the measures into columns rather than the rows, then used the calculate field option to calculate the GP%

    Thank you though

  4. #4
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: Pivot Construction

    Hi i think i managed to suss this out by adding in a "calculate item feld"? problem being now, is that my data is quite large, and it just crashes excel every time i try and insert his new item.

    I have completely gave up and am trying a new approach lol

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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