3 Replies Latest reply on Mar 28, 2012 8:48 PM by Pavan Tadepalli

    Filtering by Higher Level Aggregation

    Michael Greenwell
    Michael Greenwell Jan 19, 2012 4:35 PM

      In the attached workbook on the Booked Revenue by Company worksheet I have a bar graph sorted by Revenue. I then apply colour to different sections of the graph based on the AM responsible for the sale. I want to put a filter on the graph and the table below it that will exclude companies that don't meet a minimum revenue requirement, but whenever I add that filter it wants to summarize by both Company and AM instead of just Company. I thought there would be a solution to this somewhere in the table calculations, but I haven't been able to come up with anyone. Can anyone help me create a quick filter that will filter companies by all of their revenue while still colouring the graphs with the contribution of specific AMs?

       

      Thanks,

       

      Mike

      • 235 Views
      • Categories: Actions & Filters, Formatting & Display, Views & Dashboards
      • Tags:
        • 1. Re: Filtering by Higher Level Aggregation
          Richard Leeke
          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
            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
              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