I am trying to program a calculated field for an aggregated forecast accuracy rate for a report. This rate involves my aggregating all values in the Absolute Error column and dividing the result with the total Actuals value. I am only using total values, so all relevant data in the completed report will be in the Grand Total columns (auto-calculated by Tableau). The aggregated forecast accuracy rate is in the Grand Total cell in the Forecast Accuracy column, and I have written a program to manipulate that cell. In the image below, the 64.14% is the Grand Total cell of the Forecast Accuracy column.

However, the total of the Absolute Error column (ABS([Error])) is just the absolute error of the total of the Error column, so it's not correct. I have used WINDOW_SUM to program a table calculation that sums all the rows of the Absolute Error column.

I currently have the 64.14% calculated from manually inputting 53,711,626 in the calculated field. [Feb-Apr Forecast Accuracy] is a calculated field that is used to calculate the accuracy rate for individual items.

Is there way to automate this process by pulling the value from a cell of that column so that it's not hard-coded?

Since I'm pulling the data from a Tableau server, the issue with the different datasources is that the Forecasts and the Actuals come from two different datasets. Thus, Error, Absolute Error (hence Error Total), and the Forecast Accuracy Rate are all calculated with data from two different datasources. Hence, when attempting to use an LOD expression in Error Total, Tableau automatically disqualifies that because Error Total is calculated by Error. I'm not sure how to duplicate that situation without pulling into a Tableau server. Do you have any advice on looking up the datasources and organizing Excel spreadsheets to replicate this problem? I'm happy to provide you with the codes from the different measures that I have problem with if that also helps.

I understand your situation now. But with two data sources and blending, it is not going to have solution in your case unless you can do some sort of join in advance and publish as new data source in tableau server.