1 Reply Latest reply on May 14, 2012 7:27 AM by Jonathan Drummey

    Difference Between Percent of Total (Down) Across Years?

    Robin Sarac

      In the screen capture below, I'm calculating a simple percent of total (table down).  What I would like to do is add a column that tells me how these numbers have changed for each age group across the years.

       

      So, for the 18 to 24 age group, the 2011 percentage is 38.23% and the 2004 percentage is 40.43%.  I would like another column that reports the change from 2004 to 2011, or 38.23% - 40.43% = -2.2%

       

      I've tried various combinations of SUM() / TOTAL(SUM()) using things like LOOKUP (, -1) but I'm stumped.  I think my problem stems from the fact that I need to partition along the table down, but then do the LOOKUP along the table across.

       

      If anyone has any idea how I could do this, it would be very much appreciated!  I'm sure this has come up before, but I couldn't find anything in the forums.  I may not be using the correct terms?

       

      Percent Total Difference.bmp

        • 1. Re: Difference Between Percent of Total (Down) Across Years?
          Jonathan Drummey

          Hi Robin,

           

          Attached is a workbook that does this using the Superstore Sales data. What I did was:

           

          - Created the CNT(Order ID) aggregation.

          - Used the Quick Table Calculation to create a Percent of Total.

          - Clicked on that Percent of Total pill, selected Edit Table Calculation..., then Edit Formula to create a calculated field for the % of Total Orders.

          - Created another calculation called % of Total YOY Difference with the following formula:

           

          ZN([% of Total Orders]) - LOOKUP(ZN([% of Total Orders]),-1)

           

          - Then brought that calculation into the view, and set the Compute Using to Table (Across).

           

          Let me know if this works for you,

           

          Jonathan

          1 of 1 people found this helpful