+ Reply to Thread
Results 1 to 3 of 3

Need help with a calculated item in a pivot

  1. #1
    Forum Contributor
    Join Date
    09-05-2007
    Posts
    148

    Need help with a calculated item in a pivot

    I have a pivot with data that looks like this. CY and LY are from my data set and CY-LY is a calculated Item.

    CY LY CY-LY
    Country 1 Measure 1 20 10 10
    Country 1 Measure 2 30 20 10
    Country 1 Measure 1/ Measure 2 67% 50% 17%


    I want another column which is the % difference between CY and LY. It's easy enough to do, just use a Calculated Item and do CY/LY but I don't want it to calculate for the Measure 1/measure 2 as it would be the % change for a % difference which isn't useful to me.

    Is there anyway that I can get the calculated item to do a % difference but only for Measure 1 and 2? I would guess an if fucntion but don't know how to write it so that it looks for a %.

    Thanks

  2. #2
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Need help with a calculated item in a pivot

    If I understand you correctly, then no - a calculated item will calculate for every row that the component items appear.
    Good luck.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Need help with a calculated item in a pivot

    I have sometimes put a column of calculations outside of a PT, and had references into the PT to get results that would otherwise be too complex or not possible/practical within the PT. I added some "if" to the formula to hide it when the PT "shortens", make sure to copy the formulas down to as long and the PT will stretch to
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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