+ Reply to Thread
Results 1 to 9 of 9

Pivot tables: can't add calculated item on grouped fields

  1. #1
    Registered User
    Join Date
    04-14-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Pivot tables: can't add calculated item on grouped fields

    Hello,
    I saw that this problem is known but could not find a workaround or a solution.

    I wanted to make a simple pivot table to track incomes/expense etc.
    I made a sheet where I add my transactions divided by categories (for example salary) day by day.

    Creating pivot table I used date as columns and categories as rows.
    Automatically it groups date by month and years.

    To add the calculated item (example net = income-expenses) I had to ungroup the dates.

    After that I would like to group again by month and years but it's not possible anymore after having added the net item.
    Is there a way to obtain this result?

  2. #2
    Registered User
    Join Date
    04-14-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: Pivot tables: can't add calculated item on grouped fields

    Here you can find an example.
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,546

    Re: Pivot tables: can't add calculated item on grouped fields

    Perhaps the pivot table on Sheet1 is what you want.
    1. Reduced the size of the source table to include only those rows that had data.
    2. Loaded the table on the Pivot sheet into the Data Model
    3. Added the following measure > Net:=CALCULATE(SUM(Table5[$]),Table5[Cat]="Income")-CALCULATE(SUM(Table5[$]),Table5[Cat]="Expense")
    4. Produced the pivot table on Sheet1 from the Data Model with Cat and Sub in the Rows area, Data(Year) and Data(Month) in the Columns area and Net in the Values area.
    5. Turn off subtotals
    6. Only show grand total for columns
    Note that originally the "Net " in cell B10 displayed Grand Total but I changed the label.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    04-14-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: Pivot tables: can't add calculated item on grouped fields

    Thank you for this solution, it is exactly what I was looking for.

    Is there a way to add other formulas like the net you added? For example I can have a formula to sum up some kind of data and a second formula to consider others.
    Like to have multiple of the net that you add.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,546

    Re: Pivot tables: can't add calculated item on grouped fields

    If I understand correctly you would like to have a measure that finds the difference between one attribute and all of the other attributes, such as the difference between the Salary sub category and any other sub category.
    If that is correct try > Net2:=CALCULATE(SUM(Table5[$]),Table5[Sub]="Salary")-CALCULATE(SUM(Table5[$]),Table5[Sub]<>"Salary")
    If I have misunderstood please provide us a more specific example of what you would like the formula to do.
    Let us know if you have any questions.

  6. #6
    Registered User
    Join Date
    04-14-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: Pivot tables: can't add calculated item on grouped fields

    Thanks, I was able to add the second measure as you suggested, but when I add the second measure it is shown only in the columns.
    For example using Net and net2 I have both on colum and only Net in rows.
    Is it possible to set both of them on rows?

  7. #7
    Registered User
    Join Date
    04-14-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: Pivot tables: can't add calculated item on grouped fields

    To make it clearer I would use this example. I want to add different formulas at the bottom row such as

    1 Investment Gain1 = Value at Qtr2- value at Qtr1 and so on (could be negative->loss)
    2 Investment Gain2 = $ Deposit - $ Whitdrawal (could be negative ->loss)
    3 Total Income = Salary + IG1 + IG2
    4 Tax = 0.3*Salary + 0.26*IG1
    5 Net Incomes = Total Income - Tax

    Expenses is a category so no need for formulas

    6 Net Total = Net Income - Expenses

    Then I would like to show all these intermidiates formulas from 1 to 6 and plot in a bar chart:
    Bar 1 positive = Net Incomes
    Bar 2 negative = Expenses
    Then a line that represent the Net total

    The problem is that your solution seems working fine untile I only had one formula. After adding different formulas it add the other formulas as field (column) and only the first one is computed in the rows.
    That's why I was looking for a way to add calculated item because with calculated item I'm able to do that but then I have to forget about grouped field (data as Year/Qtr).

    Is there a solution to do this?
    Can I use more than one fomrula as you said but in the rows?

    Thanks for your patience.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,546

    Re: Pivot tables: can't add calculated item on grouped fields

    I am traveling so not able to work on this at this time. Will look at it when I get home unless another contributor has solved it.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,546

    Re: Pivot tables: can't add calculated item on grouped fields

    Not totally sure that I understand what you want, however I feel that it would need to be accomplished using either formulas or VBA.
    Please provide some source data corresponding to post #7 and then manually mock up the output you want so that we can attempt to replicate your output using Excel functionality.
    Let us know if you have any questions.

+ 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. Calculated Fields in Pivot Tables
    By nicoan in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-10-2014, 02:35 PM
  2. pivot tables + calculated fields
    By mrexcelis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-13-2014, 03:42 PM
  3. Replies: 0
    Last Post: 11-01-2013, 11:45 AM
  4. Pivot Tables and Calculated Fields
    By ciw916 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-04-2013, 04:12 AM
  5. pivot tables containing calculated item ... ???
    By cashflowpro in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-26-2009, 07:49 AM
  6. [SOLVED] Pivot Tables - how do I add in calculated fields?
    By ColinS via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-06-2006, 12:45 PM
  7. [SOLVED] Calculated fields in Pivot Tables
    By lj in forum Excel General
    Replies: 2
    Last Post: 11-15-2005, 06:20 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