• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Tables - Add Calculated Field with Grouping

S

Shaun

Member
Hi All,

I am trying to insert a calculated field where the values to be inserted are 50% of the total of a grouped set of columns.

I have found many examples of ungrouped pivot tables, but not on grouped tables.

In the example attached, I would like to add the calculated field of "Budget" which is calculated as 50% of the income values.

Any ideas?

Cheers

Shaun
 

Attachments

  • Chandoo Example.xlsm
    131.3 KB · Views: 10
Manually grouped columns are usually not a good idea in Pivot.

I'd recommend using additional table for Account# and Income/Cog identification. Then either link the tables through data relationship or through formula. This should allow you to use grouping and also use calculated field.

Also, don't use Alt+Enter I.E. CHAR(10) in column headers. It will cause issue down the line (when constructing calculated field, adding table to data model etc). Preferably, column header should not have space or special characters in it as well.
 
Hi Chihiro,

Thank you for you reply and suggestions, I appreciate them.

You are suggesting I use multiple tables:
  • A table for AccountNumbers; and then
  • A table for each of the groupings:
    • Income
    • COGS
    • Expenses
    • Etc
In the file I sent to you via pm, the groupings are defined by the user via user form after the Data table is imported from the database.

At this stage the Data table is generated before the groupings take place via the user form, in a sort of a capture all. Are you suggesting that the groupings should take place before any data is imported from the data base? Once the groupings are defined, then the import takes place?

Then create the pivot tables/charts from multiple table in the work book?

Thank you,

Shaun
 
That would be right.

Basically, you'd want each account# defined as one of the types you mentioned in either separate column or table.

I tend to find having separate table with all unique account# with corresponding grouping easier to maintain, rather than additional column. It's quite easy to link two tables via Data Relationship, PowerQuery, MS Query or Formula depending on your needs.

Having this column/table will ensure ease of data transformation and analysis down the line.
 
Hi Chihiro,

I think I should be able to make the changes as suggested.

Can I still use the grouping mechanism (as is in the file provided via pm), which yields a | delimited string (4-1000|4-1001|4-1002|etc)?

Cheers,

Shaun
 
Actually, what I meant is to have all of Income, Expense & Cogs combined in same table.

Define AccountType & Subtype along with what you currently have in Chart of Accounts.

Then link Chart of Accounts and combined table using Data Relationship.

Construct Pivot using Data Relationship (add calculated field which will be, Income*.5)

I'll be out running errands, but will see if I can give you example construction later tonight.
 
You must log in or register to reply here.
Back
Top