
1. Re: How to calculate from different dimensions
Patrick Van Der Hyde Jan 8, 2019 11:53 AM (in response to Yao Lu)Hello Yao,
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.
Thank you
Patrick
Tableau Community Manager

sample_PV.twbx 36.7 KB


2. Re: How to calculate from different dimensions
Yao Lu Jan 11, 2019 11:42 AM (in response to Patrick Van Der Hyde)Hi Patrick,
Thanks for your help very much. I will learn it carefully.
Thanks again,
Yao