Cannot total complex Lookup() table calculation over categorical dimension
Andrew Prince Dec 8, 2016 6:10 PMEdited original post to attach solution workbook: (Forecast_Tool  Solution)
I have been working on this for a couple of days but cannot figure how to get a total of the depreciated cumulative asset class values for each year.
Advice, solutions or different approaches will be much appreciated!
Background:
 The attached workbook contains a cut down version of a forecasting model.
 The subject of the forecast is depreciation of assets over time plus the forecast CAPEX investment to replace or increase those assets.
 Clicking the hamburger icon in the 'Forecast Tool' dashboard reveals all of the user input parameters
 The intention is to allow the end user to input the forecast capital expenditure in any of the next 10 years if they do not like the base forecast (calculated in the dataset)
 Each year's expenditure is split into 'Asset Classes' such as Buildings, Property & Equipment, etc. Each of these asset classes has a different Depreciation Rate that can be input by the user
Primary Issue:
 The calculation for the each year's closing Written Down Value (WDV) (the depreciated value) is:
 For each year and for each individual Asset Class:
 (Prior Year WDV + Current Year Forecast CAPEX)  (Prior Year WDV + Current Year CAPEX) * Asset Class Depreciation Rate
 For each year and for each individual Asset Class:
 Due to the user inputs I would like to include in the model, it appears that the only way to calculate the above is by using the following Table Calculation (named [Cumulative WDV pt2] in the workbook) :
"IF [AC1 Parameter] = ATTR([Asset Class Name])
THEN
ZN(LOOKUP([Cumulative WDV pt1],1) + PREVIOUS_VALUE(1))

(ZN(LOOKUP([Cumulative WDV pt1],1) + PREVIOUS_VALUE(1)) * MIN([AC1 Depn Rate Input]))
... END"
 However, because I am calculating the value individually along the Asset Class categorical dimension I am not able calculate the total (all asset classes) of each year's closing Written Down Value.
 Solving this is important so that I can use the total to calculate the year on year change in the Asset Base
 As you can see in the below image I am forced to include the Asset Class dimension on the Details shelf:
Other Constraints:
 Due to security concerns the workbook must be fully packaged with extracts (distributed via Tableau Server) so no parameters + custom SQL allowed.
 It is important that the end user can tweak aspects of the model  therefore the large number of input parameters (and the above question)
 Solution or approach must be available in Tableau version 9.0 (annoying I know)

Forecast_Tool  Solution.twbx 436.7 KB

Forecast_Tool.twbx 478.1 KB