Calculate Ratio Diagonally

Hi Ali,

I believe I understand what you are trying to do and the answer is yes, you can use Table Calculations to do this.  I've trimmed back your table just a bit so that I can show everything on the view.  So I am starting with a table of years and Pre-K - 4.

What we want to do is look back one year and back one grade, correct?  So to do that, I used two Table Calculations for clarity.  First, lets create a Table Calculation to pull the value for the same year, previous grad.  So for K 2012 (398) we will pull PreK 2012 (495).

    @Lookup Value Same Year Previous Grade

          LOOKUP(sum([TOTAL]), -1)

I set up a second table using measure names and measure values and added this calculation to the Measure Values pane.  The table calc is set up as follows:

Now, we have the same year previous grade, we can use that to determine the value above it, i.e. the previous year, previous grade.  So I created a second table calculation using the first table calculation and we will basically set it to choose the value above it.

    @Lookup Value Previous Year Previous Grade

          LOOKUP([@Lookup Value Same Year Previous Grade], -1)

I added this one to Measure Values and set the table calculation as table down:

You'll see it now chooses the correct value of 452.  From there, we just create one last calculation to calculate the ratio of the original figure 398 and this figure 452.

    @Ratio

          SUM([TOTAL])/[@Lookup Value Previous Year Previous Grade]

Add that to Measure Values and you have the result you desired:

I've attached the workbook for you to look at - Please see the green worksheets.  (I attached a current version and one for 2018.3).  Please let me know if this solved your issue or if you have additional questions.

Thanks,

Kevin Flerlage