Locking a Set Analysis Result in a table like Excel $

[caseyjohnson]

I have a simple table and I'm trying to figure out how to lock in a calculation using set analysis (or something else?) for the calculation of other fields. So far, I'm striking out. What I'm trying to do is use a certain field like you would in Excel (ex: $C$15) to make some calculations throughout the table.

I'd like to be able to use the 2018 Avg Sales to calculate the percent changes throughout the table without hard setting a value in the calculation. Would need it to be able to accept Dimension changes as well.

Table:

Year      Sum(Sales)     Avg(Sales)    Percent Change (in Avg)

2018     20000              500               0%

2017     17500              425               ?   

2016     15000              413               ?

2015     13000              435               ?

This was my attempt:

Avg(Sales) -  Avg($< Year = {2018}>} Sales) / Avg($< Year = {2018}>} Sales)

So for 2017, it should show a (-15%) change and so on.

[sunny_talwar · Accepted solution]

There are two ways you can do this... if you always want to divide by 2018's avg sale... try this

Avg(Sales)/Avg(TOTAL {$<Year = {2018}>} Sales) - 1

If you want to divide by the avg sales of next year, then try this

Avg(Sales)/Above(Avg({<Year>}Sales)) - 1

[caseyjohnson]

That's it! Thank you Sunny!!