I have a table of transactional data with multiple rows (thousands) I am wanting to summarise with a pivot.
Columns include cust_id, transaction_id, date, amount (among other things).
I am using the excel feature where I select date as my columns, and excel automatically chunks the data into month, quarter and year. I am wanting the person viewing to be able to switch between levels (eg have a look at month, then switch to quarter etc)
This is all good for simple views such as "total amount" (by date), or total number of applications, or total customers etc....no problem.
Where I'm stuck is I want to view "average amount by customer, by date", or "average amount of transaction, by date".
Is this kinda of nesting even possible in a pivot? I've started thinking perhaps one pivot to provide totals, then a second to compute the averages.....
Anyone any ideas? Thanks heaps