-
1. Re: Filtering by Higher Level Aggregation
Richard Leeke Jan 19, 2012 5:30 PM (in response to Michael Greenwell)You're on the right lines with your [CompanyRevenue] table calculation - though it needs to be over the whole partition - you have it looking at just one row.
You need to put that on a shelf (say Level of Detail), set the partitioning to Compute Using [AM] (which means it calculates the total over all AMs for the Company) and then copy the field onto the filter shelf.
You could also use TOTAL() instead of WINDOW_SUM() in this case.
Note that as the workbook is set up, that filter will be on the total for all selected AMs for each company, not the overall total for each company. If you want the overall total, but still want to be able to filter the display by AM there is a bit more table calculation trickery needed (but not too tricky).
-
2. Re: Filtering by Higher Level Aggregation
Michael Greenwell Jan 20, 2012 8:34 AM (in response to Michael Greenwell)Thank you Richard, that is exactly what I was looking for. I kept trying to summarize by Company instead of by AM as I was mistakenly thinking of Table Calculations as being like SQL aggregation.
-
3. Re: Filtering by Higher Level Aggregation
Pavan Tadepalli Mar 28, 2012 8:48 PM (in response to Richard Leeke)Hi Richard,
I am in a similar situation where in I filter for a Region (say "Central Region"), but I want the Window_Sum to show the totals for all regions. I saw that in this thread you mentioned that there is a way to do that. Can you please help me with this? May be we can use the same packaged worbook as an example if thats okay?
"If you want the overall total, but still want to be able to filter the display by AM there is a bit more table calculation trickery needed (but not too tricky)."
Thanks
Pavan