Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Grand total average for sum sales per month

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Grand total average for sum sales per month


    Grand total average for sum sales per month
    I have a sales fact table and created a pivot table like this:

    Month Value

    Jan 200.00
    Feb 300.00
    Mar 500.00

    Grand Total 1,000.00

    I want to change only the Grand Total to Average function:

    Month Value

    Jan 200.00
    Feb 300.00
    Mar 500.00

    Grand Average 333.33

    Is this possible?

  2. #2
    Board Regular
    Join Date
    Jun 2009
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grand total average for sum sales per month

    Go to Field Settings on the Ribbon, or click on the values field in the Pivot Table Field List under the values field, click on the dropdown arrow and select value field settings. Then select Average from the box in the middle.

  3. #3
    New Member
    Join Date
    Apr 2012
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grand total average for sum sales per month

    This is not the solution I want, because it changes the monthly sum sales to monthly average sales.
    I need sums per month and average in the grand total.

    I have an idea: two pivot tables controlled by the same slicers.

    The first pivot table with the sum sales per month and the grand total hidden.

    The second pivot table without the detail of months using average and the grand total hidden. The second pivot table working as the grand total of the first pivot table. I'm going to try this idea tomorrow.

    Anyone with an easier solution?

  4. #4
    Board Regular
    Join Date
    Aug 2008
    Location
    New York
    Posts
    171
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grand total average for sum sales per month

    First create a measure ValueSum:=SUM(Table1[Value])
    Then create a measures that takes the average of the monthly totals of the previous measure.
    Code:
    =AVERAGEX(VALUES(Table1[Month]),[ValueSum])

  5. #5
    Board Regular
    Join Date
    Jan 2012
    Location
    Ohio
    Posts
    242
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grand total average for sum sales per month

    Full blog post on making totals and grand totals add up differently than their individual row or column values:

    http://www.powerpivotpro.com/2012/03...-up-correctly/

  6. #6
    New Member
    Join Date
    Apr 2012
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grand total average for sum sales per month

    At the end my solution is the grand average outside of the pivot table ...

  7. #7
    New Member
    Join Date
    Aug 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grand total average for sum sales per month

    Quote Originally Posted by ruve1k View Post
    First create a measure ValueSum:=SUM(Table1[Value])
    Then create a measures that takes the average of the monthly totals of the previous measure.
    Code:
    =AVERAGEX(VALUES(Table1[Month]),[ValueSum])

    This solved it for me! Thanks!

  8. #8
    New Member
    Join Date
    Sep 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grand total average for sum sales per month

    The way i solved this issue:

    Right click Pivot Table
    Select "Pivot Table Options"
    Go to "Totals and Filters" tab
    Deselect "Show grand totals for columns"

    Then simply do an average function below the table (not in the table) and select the data you want. You can also select additional cells so that as your database grows (and your pivot table with it), you can simply refresh your pivot table and the averages will update automatically.

  9. #9
    Board Regular
    Join Date
    Aug 2008
    Location
    New York
    Posts
    171
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grand total average for sum sales per month

    peteyian1,

    Your method will return an average of all values in the underlying data.
    The original poster requested an average of the monthly totals.
    These are two different calculations.

  10. #10
    New Member
    Join Date
    Sep 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grand total average for sum sales per month

    No, you aren't changing the functions within the pivot table, so it still returns rows that sum sales per month from the database. You write the average function outside the pivot table so that it simply references the pivot table data.

    Quote Originally Posted by ruve1k View Post
    peteyian1,

    Your method will return an average of all values in the underlying data.
    The original poster requested an average of the monthly totals.
    These are two different calculations.

Some videos you may like

User Tag List

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
  •