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

# Difference Between Percent of Total (Down) Across Years?

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?

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

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