I am trying to figure out a way to do weighted averages using data blending and was hoping to get some pointers. Here is my scenario:
I have a primary data source with salary information. I have a secondary data source with salary indexes by location. The indexes apply to salaries that are between specific values. For example, Tucson may have a salary index of 50 for salaries between 35000 and 50000 and a salary index of 75 for salaries between 50001 and 75000. These two files are blended together linking on the common field of work location.
I am trying to write a formula that takes the number of employees that fall into each salary range bucket, multiply that by the salary index for that bucket, add all that up, and divides by the total number of employees at that location. Here's a detailed example:
Suppose Tucson had 10 people earning the following:
And suppose I had three indexes:
50 for salaries between 0-20000
75 for salaries between 20001-35000
100 for salaries above 35001
I would like to build a table calc that produces a value of 65 for Tucson, calculated as:
(5 emps. earning 20K or below x index of 50 +
4 emps. earning >20K-35K x index of 75 +
1 emp. earning >35K x index of 100) / 10 emps
I can bucket the employees into each grouping but I'm not sure how to create the table calc to do the weighted average.
I'm sure this is possible in Tableau but I cannot figure out how I'd do so. I am assuming I'd need to do at least three sets of calculations (one to bucket the employees into the appropriate buckets, one to take the counts of each bucket and multiply by the city-appropriate index for that given bucket, and one to sum up everything and divide by the number of employees at each city), but after that I'm kind of lost.
Any advice would be appreciated. I've taken a look at the two knowledgebase articles on weighted averages but they are not at the level of complexity that I think I'm wrassling with. (I've got data dimensions to consider as well, but I'm hoping that Tableau's filtering functionality will take care of that for me).