PivotTable: average column of values divided by 'Columns' field. PowerPivot solution?

Hi!


I want to insert a column in a PivotTable, which calculates the average of four other columns. This can not be done with a calculated field, because the columns are created using the 'Columns' field in the Field list. Terrible explanation, I know, so I tried to attach a demonstration file, but it doesn't work (clicking button does nothing) so please have a look at the attached picture instead. I want to create a column in the PivotTable as in Column J, but within the PivotTable.

I suspect that this is possible only if I load the table into PowerPivot and create a measure? I'm only learning PowerPivot, so I don't know exactely how this would be done..


Thank you,
Alfred

issue.JPG

Originally Posted by Richard Buttrey

Hi,

The averages you show are not the true average of your data - except in the unlikely case that each quarter has exactly the same number of values.
You shouldn't average averages. You should average the original data.

Upload the workbook - see the FAQ and maybe we can help further

Hi Richard,


Thank you for your reply. I'm not calculating the averages of averages, but averages of sums.


Alfred

Edit ok I'll check the faq

Voilą the file.. hope this clarifies my question.

You can do it with Power Pivot but you will need an additional table with just the 4 quarters in it because you don't have data for every product for every quarter but you want to treat missing data as 0.

Assuming your existing data is Table1, and the new Quarters table is Table2, you then need to add a relationship between the quarters in the two tables.

Then you need to add a measure to Table1 called Total_Sales as:
=IF(COUNTROWS(Table1)>0,SUM(Table1[Sales]),0)
and another called Month_Average as:
=AVERAGEX(VALUES(Table2[Quarter]),[Total_sales])

Once you have added the two measures, you just need to create a set based on the column fields to hide the averages for each quarter and the grand total sum so that you just have the sums by quarter and the total average showing.

Thank you rorya, that worked perfectly!

Richard, my real data shows sales levels for the products, split by different categories in the ERP system. I want to see the total sales of each product per quarter, and the the average sales per quarter. For 'Discovery' the total sales in Q1 is 1305 + 1932 = 3237, in Q2 it's 0, Q3 653, Q4 0. Hence the average sales, per quarter, is 973. The number 1297 is not interesting in this context.