rhampson100
New Member
- Joined
- May 27, 2015
- Messages
- 2
Excel 2007
windows XP
I am trying to group columns of dates so that they show either quarters or years when the initial data is by month.
The problem I have is that I want the results to show the total qrt/year as an average in the 'summarise value field by' within the field settings. However this then averages the values against the rows when they should be summed.
As a simple example, if I have a field called customer in the report filter, a field called measure in the rows with 'average balance' as the item under this field. The months are in the column labels and the balance value is in the value section. If customer A has an average balance Jan to Mar of 1200, 1500, 1200 and customer B has average balances of 600,500,400 then both customers should correctly sum to 1800, 2000, 1600 - to get to this I have the value field setting to summarise as sum. But to see the correct average of the average balance across the qrt, customer A should have a qrt average of 1300 and customer B should have 500. This requires the value field settings to be set to average rather than sum.
Please can anyone tell me if I can mix these settings so i can see the correct sum of monthly averages and grouped quarterly averages?
many thanks
windows XP
I am trying to group columns of dates so that they show either quarters or years when the initial data is by month.
The problem I have is that I want the results to show the total qrt/year as an average in the 'summarise value field by' within the field settings. However this then averages the values against the rows when they should be summed.
As a simple example, if I have a field called customer in the report filter, a field called measure in the rows with 'average balance' as the item under this field. The months are in the column labels and the balance value is in the value section. If customer A has an average balance Jan to Mar of 1200, 1500, 1200 and customer B has average balances of 600,500,400 then both customers should correctly sum to 1800, 2000, 1600 - to get to this I have the value field setting to summarise as sum. But to see the correct average of the average balance across the qrt, customer A should have a qrt average of 1300 and customer B should have 500. This requires the value field settings to be set to average rather than sum.
Please can anyone tell me if I can mix these settings so i can see the correct sum of monthly averages and grouped quarterly averages?
many thanks
