I want to preface this - This is not an easy solution and depends on your knowledge of Table Calculation functions to implement. That said - with some time and careful learning, you too can master these if needed.
I would first suggest trying to adjust the raw data into a single data source or one that could be joined.
Here is a working solution (workbook attached)
The calculations created - A new benefit Value field - if first()=0 then window_sum(sum([Benefit Value])) end
A new windows calc to avg HC in the HC data - window_avg(sum([HC]))
A new TC HC Avg field in the primary data source - if first()=0 then [HC (sample)].[windows calc for Avg HC] end
A new Year field - datepart('year',[FY]) that has been made into a Dimension.
Each of these has been defined uniquely to utilize the FY field as a dimension to include when determining the Avg since the average is across all of the values of FY (a series of dates) within a single Year.
Each of the table calculations utilizes IF First()=0 then .... end - because the calculations will return a result for each instance of FY per cell and we only want one instance returned.
Note that any additional dimensions added to the view will impact the calculation of the Table Calculations. You may want to invest time in learning details of Table Calculations in Tableau if you do not have much familiarity with them.
Best of luck and if you found this to resolve your issues here, please mark the post correct.
Tableau Community Manager
sample_PV.twbx 36.7 KB
Thanks for your help very much. I will learn it carefully.