Closed Thread
Results 1 to 19 of 19

Pivot Table, Calculated Item.... No Data, still creates a row

  1. #1
    Registered User
    Join Date
    10-12-2007
    Posts
    21

    Pivot Table, Calculated Item.... No Data, still creates a row

    Hi everyone.

    I have a Pivot table.

    Layout:
    Rows: City, Restaurant, Cashflow
    Column: Date (Month/Year)
    DataField: $Amount

    "City" might be 7 cities
    "Restaurant" may be 32 restaurants, some in some cities; but not other
    "CashFlow" can be either "Revenue" or "Expense"

    The layout of the Pivot table is nice, only shows the Rows where a State, Restaurant, and CashFlow entry exists for at least one Date on that row. And there are no extraneous rows for combinations that don't exist.

    Now.... the trouble...

    I added a Calculated Item, "Profit" which = "Revenue" - "Expenses".
    Now, every possible combination of State and Restaurant appears in the pivot table displayed. Only the "Profit" (calculated item) is shown for the previously hidden rows; and of course it's "$0.00" since there are no "Revenue" or "Expense" entries.

    How can I get the Pivot table to not display the results of a Calculated Item row, when there are no entries otherwise for that row combination?
    Is there a way to condition the Calculated Item to not calculate if there are no data entries in the addends for a particular combination?

    thank you very much.

    I'm somewhat handy and will be glad to help others; as this is my first post here.

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075
    Try putting an IF statement around the calculation formula, like this:

    =IF((Revenue-Expenses)=0,"",Revenue-Expenses)
    Trish in Oz
    -------------
    A problem well defined is a puzzle half solved


    If you attach a sample of your workbook it will be easier to find a solution. Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
    http://www.excelforum.com/faq.php?fa...b3_attachments

  3. #3
    Registered User
    Join Date
    10-12-2007
    Posts
    21
    Quote Originally Posted by tuph
    Try putting an IF statement around the calculation formula, like this:

    =IF((Revenue-Expenses)=0,"",Revenue-Expenses)
    thank you for your response.

    the problem is that it still creates a row for this entry, even if there are no Revenues or Expenses (because a particular Restaraunt is not in that particular city)

    Once I create "Calculated Item" or "Calculated Field" Excel will automatically provide a row for output of that item or field, even when there is no base data associated with that combination of parameters.

    I wish it did not.

  4. #4
    Registered User
    Join Date
    11-12-2007
    Posts
    5
    Quote Originally Posted by Oscar
    thank you for your response.

    the problem is that it still creates a row for this entry, even if there are no Revenues or Expenses (because a particular Restaraunt is not in that particular city)

    Once I create "Calculated Item" or "Calculated Field" Excel will automatically provide a row for output of that item or field, even when there is no base data associated with that combination of parameters.

    I wish it did not.
    Hi all. I'm a brand new user here, so I would like to thank everyone for all of the input so far.

    I am having this exact problem...exactly as Oscar has described. I've searched everywhere and cannot find a solution. The end result is that my pivot table is about 900 rows long when it should only be about 50...

  5. #5
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075
    Sorry it's been so long. Are you able to zip and attach a copy of your workbook? It might help to actually see the problem.

  6. #6
    Registered User
    Join Date
    11-12-2007
    Posts
    5
    Quote Originally Posted by tuph
    Sorry it's been so long. Are you able to zip and attach a copy of your workbook? It might help to actually see the problem.
    Thanks for taking a look tuph. It's a large file, but it's attached.

    Ok, take a look at Column F...this is the column w/ the created item in it. As you can see, all of the "Carriers" (Column B) are shown in their own row, even if no data is associated with it. I put a null function is as the last command of the formula so they would show null, but otherwise they all would show as 0. I hope you can help...thanks for taking a look.

    Blake
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075
    I suspect it's something to do with column E on the Data worksheet which has "Y"s against all of the factory rows. I can't test it though because I can't make the pivot table refresh correctly unless I delete all data from column E in the Data worksheet. Weird! Maybe it's a version thing, but I've saved it several times and still can't make it behave.

    Someone more competent might pick this up and have a look for you.

  8. #8
    Registered User
    Join Date
    11-12-2007
    Posts
    5
    Try this file out to see if it works...thanks for looking at it. Also, I'm using version 2003.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075
    Doesn't make any difference. If I delete all data from Can Contert? column the table refreshes to a single Grand Total line, but if I leave even one cell with data the whole table shows Y against every row. I've done very little with calculated fields in pivot tables so don't have much background knowledge.

    Sorry I can't be more help.

  10. #10
    Registered User
    Join Date
    11-12-2007
    Posts
    5
    Hey, no worries...thanks for looking at it though!

  11. #11
    Forum Contributor
    Join Date
    10-30-2007
    Location
    Norway
    MS-Off Ver
    MS Office 2007
    Posts
    345
    Oscar:
    I think there is no way around this problem when you use calculated item.

    What you have to do is add 2 extra fields to your source data. This fields is "revenue" and "expences". Use an IF formula to populate these data.

    In your pivot table remove the "Cashflow" and "amount" field. Instead you put "Revenue" and "Expences" in the data area. Now you can calculate "Profit" as a calculated field instead of a calculated item. Calculated field should not have the same problem.

    mbgibbs13:
    I have had a look at your workbook, but are not sure what you are trying to calculate with your calculated item. Can you too find a workaround by using calculated field instead ?
    By the way, I had no problem refreshing the pivot table.
    Last edited by Bjornar; 11-14-2007 at 03:51 PM.

  12. #12
    Registered User
    Join Date
    11-12-2007
    Posts
    5
    I tried to use a calculated field before I tried the calculated item, and I wasn't able to get the result I'm looking for. Maybe I'll play around with it again to see if I can figure out how to make it work. I agree now that it seems there is no way to get around this issue using a calculated item. Thanks again.

  13. #13
    Registered User
    Join Date
    04-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Pivot Table, Calculated Item.... No Data, still creates a row

    There is a way to get rid of the 0 rows. You have to value filter in the column of your choice. I believe in this case it will be city. Please let me know if it helps.

  14. #14
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Pivot Table, Calculated Item.... No Data, still creates a row

    The thread being more than 5 years old, I doubt the OP is still looking for an answer....

  15. #15
    Registered User
    Join Date
    04-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Pivot Table, Calculated Item.... No Data, still creates a row

    Oh I see. Thanks for the heads up.

  16. #16
    Registered User
    Join Date
    07-09-2020
    Location
    Utah
    MS-Off Ver
    365
    Posts
    3

    Re: Pivot Table, Calculated Item.... No Data, still creates a row

    Very old thread. But I'm having the same issue. Hoping to revive to see if anyone has a solution

  17. #17
    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,917

    Re: Pivot Table, Calculated Item.... No Data, still creates a row

    Quote Originally Posted by elderwand View Post
    Very old thread. But I'm having the same issue. Hoping to revive to see if anyone has a solution
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    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

  18. #18
    Registered User
    Join Date
    11-13-2022
    Location
    Riyadh
    MS-Off Ver
    2009
    Posts
    2

    Re: Pivot Table, Calculated Item.... No Data, still creates a row

    Hello everyone.

    I have the same problem. Can someone help me ?

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Pivot Table, Calculated Item.... No Data, still creates a row

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed 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