Calculated Fields in PivotTables...any trick/workaround to get an "average" to work as the aggregator?
Results 1 to 3 of 3

Thread: Calculated Fields in PivotTables...any trick/workaround to get an "average" to work as the aggregator?

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculated Fields in PivotTables...any trick/workaround to get an "average" to work as the aggregator?

    My understanding of Calculated Fields in PivotTables is that "sum" is used to aggregate a reference to a PivotTable field, and then the formula you define for the field is applied to that sum. I have verified that this behaves as stated.

    Does anybody know of a workaround to get a CalculatedField in a PivotTable to use the average? Of course you cannot manually compute that average as SUM/COUNT as you can only use SUM and can't use COUNT, either!

    So, lets say Field1 as a calculated field in a PivotTable is defined as:
    Field1 = income * 2
    where "income" is a field in the PivotTable's data range

    If what i want for the result of Field1 is: (AVERAGE of income) * 2 [not: (SUM of income) * 2] ...

    anybody know of any tricks or workarounds to get it?

    Thanks!

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,725
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Calculated Fields in PivotTables...any trick/workaround to get an "average" to work as the aggregator?

    If you have Power Pivot, it’s simple. If not, you can add a counter column to the data that just returns 1 for each applicable row so that you can sum that to get a count. You can then calculate your average using SUM as required by calculated fields.

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculated Fields in PivotTables...any trick/workaround to get an "average" to work as the aggregator?

    Thanks...that does work. Appreciate it. Always hate to "mess up the sheet" with dummy type columns, would have been nice to hide it all in a CalculatedField..but oh well, limitations do exist. As an editorial, too bad they did not just make CalcuatedFields have all the same aggregators available that PivotTables have, and allow CalculatedField language to have syntax like SUM(field), AVERAGE(field), Max(field), etc. Do appreciate the post, it will serve my purpose here!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •