You can set up an index variable that knows its position in the table. And then you can go backward from there 2 or 10 or whatever positions.
I'll play with your workbook and do something.
(V 10.1 here)
On sheet 2 I added a calc called [index]. It displays the positional index in the table. On a simple sheet like this one, using the default setting of TABLE(down) gives the right layout of index. I also created a calc called [What index did I pick?]. And I display that value in the title so you can see how you can access that index value. Once you know an index value you can do all sorts of harvesting from the table based on it.
On sheet 3 I changed [Count] from a measure to a dimension just so that you can see how addressing in table calcs works. I know [Count] as a dimension doesn't make sense, but I needed another dimension to demonstrate this. See what [index] does now? It's still set to TABLE(down), so you just get a count of 1-through-N of all the rows on the sheet. Notice also that the value of [What did I pick] has multiple values. That's because every Fruit has multiple rows.
On Sheet 4 I changed the addressing of the two calcs to work "at the level" of [Fruit]. Now you get a unique number for each fruit, and it is repeated for each [Count] under that fruit.
Fruit A.twbx 31.3 KB
This helps a lot. Do you know if there is anyway to hide rows or columns based on the index that you made? I would like to have only certain rows appear (say those with index 3 and 5), and the rest hidden from view. I know that I can manually do this, but is there a way to do this automatically?
If you use a table calc as a filter, it won't change the rows in the underlying table, but it will control what parts of the table to display.
LOOKUP() is a table calc. Using LOOKUP on offset = 0 is a common way to make a table calc to control the display. (Offset = 0 says to satisfy the equation for each current row.)
So in the attached example I made a simple filter using LOOKUP. It makes a list of values just like any other filter, and you can select any or all values to display.
If you don't want the user doing the selection, you can control it.
LOOKUP( [index] , 0 )
LOOKUP( IF [index] = 3 or [index] = 5 then 1 else 0 END , 0 )
This one will result in a value of either 1 or 0 for each [index] value, and you drag that to your filter shelf and select for value =1 and it will display rows for 3 and 5.
Fruit B.twbx 29.3 KB
That is what I was looking for. Thank you very much for your help! I marked your original answer as the correct answer, but that along with the LOOKUP function got me to where I wanted to go.