Getting a Grand Average of Counts in a Pivot Table next to the default Grand Total

[e4excel]

Dear Forum,

I need to get the Average of the Counts used in my Pivot Table, I have three Column Data
1. Col A- Branch - Name of the Branches
2. Col B- File No - Actual File Nos
3. Col C- Month- Contains Apr-19,May-19,Jun-19 and Jul-19

Now I have the Branch in my Row Section of the Pivot table, the Month in the Column Section and the File No in the Data Values where I am taking the Count.
I get the Grand Total by default, however I need to get the Average of the Counts of File Nos of the months in consideration.
I managed to do somehow using a Calculated Item however I have to manually add all the items of the months , I need a mechanism where the months get added automatically each month once the data increases every month as this is Year-To-Date Data.

In my workaround, I have created a Calculated Item to get the Grand Average of Counts and also the Grand Total , however it does not include any new months added in the data as I need to manually add it to the items, so is there a workaround without using any helper Columns in the data as i need to get this as a ready-made structure in the Pivot Table.

I can always do the Average Calculation next to my Pivot table however I need it as a part of the Pivot table.

Warm Regards
e4excel

[VKS]

Hi,

Please check the attached.
Changed the layout of your Pivot and created a new one.

Best Regards/VKS

[e4excel]

Thanks VKS for the help, however I was wanting to understand without using any Helper Columns whether achieving the requirement was possible or not?. I also need the Grand Total of Counts Columnwise and not the Average.
In your approach , I dont need the Average Total for Columns

[e4excel]

I have used the Calculated Item but I had to manually insert each item is there a way of overcoming that ?

[e4excel]

Bump a THread

[rorya]

Not if you only have 2007. Power Pivot would make this possible.

[e4excel]

Thanks a lot for the reply, can you help me with the way, I have 2010 Excel at home

[e4excel]

Bump a Thread

[e4excel]

Is there a solution to this ?
Bump a Thread

[rorya]

It's a little tricky given that you want the grand total sum as well as the overall average, so you need two parts.

First load the source data into a linked table in the PowerPivot tab. Then create a pivot table from it and create two measures:
1. A simple file count (called FileCount) using:
=COUNTA(Table1[File No])
2. An average measure using something like:
=AVERAGEX(values(Table1[Month]),[FileCount])
Adding these to your pivot table will basically create two columns for each month, so we now need to remove the monthly averages (since they're the same as the counts) by creating a set. However, in order to make sure that any new months automatically appear in the set, we actually need to use some MDX - namely:

{([Table1].[Month].AllMembers,[Measures].[FileCount]),([Table1].[Month].[All],[Measures].[AverageCount])}

(the first part shows the file count for all the available months, and the second part only show the average for all months combined)

I've done that in the attached version of your file.

[e4excel]

Wow Rorya,

This is exactly what I was looking for but I am not able to follow how to decipher it...I just started using Calculated Fields but never used Measures so where do I see what you have done exactly to arrive at this wonderful result...
I mean I am unable to understand this Pivot Table it looks very different than the commonly used Pivot Tables..

Please explain and then I am done ...I really grateful for your help ...
God Bless You...I would like to do each and every step to get into my system to use it more frequently..

[rorya]

Make your data into a Table. Load the Power Pivot add-in. Then follow the steps I outlined above to link the table to the data model and create a pivot table from it. You can then right click the table name in the Field list for the pivot table and choose Add Measure to create the two measures needed. Use the Fields, Items and Sets button on the Pivot Table tools tab to create the column Set.