8 Replies Latest reply on Jun 1, 2013 10:55 AM by Jonathan Drummey

# Create weighted average using data blended connections

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:

15000     50000

15000     35000

25000     20000

20000     25000

35000     17000

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).

• ###### 1. Re: Create weighted average using data blended connections

So sorry.  I put together a rough idea of what I'm trying to do in the attached packaged workbook.  Warning - this is a very dummied up example, and so I'm not sure how usable it would be for the purposes of solving the ask.  To help, I've attached my dummy excel data as well.  The Tableau workbook is connected to 2 different tabs of the same Excel workbook whereas in my production environment, this would NOT be the case.

Sheet 1 of the Tableau workbook has 2 work locations with a number of employees with varying salaries.  I've created a calculated field called Buckets that dumps each EE into a specific bucket based on salary.  So, for example, work location AZT01 has 6 people who are earning 35K or less.

Sheet 2 of the Tableau workbook has indexes for a given work location.  So, for example, location AZT01 has an ERI value of 92.100 for people in the 35K or under bucket.

What I'm trying to calculate is a weighted average ERI value for each work location.  So in the sample file, for work location AZT01, I would calculate:

( (ERI_35K * # of EEs in the 35K bucket) + (ERI_50K * # of EEs in the 50K bucket) +

(ERI_75K * # of EEs in the 75K bucket) + (ERI_100K * # of EEs in the 100K bucket) +

(ERI_125K * # of EEs in the 125K bucket) + (ERI_150K * # of EEs in the 150K bucket) ) / (total # of EEs in AZT01)

Does this help clarify the ask?

• ###### 2. Re: Create weighted average using data blended connections

Hi Jay,

See the attached. I needed to reshape the salary index data because it seemed like it was being set up to blend the rows of a dimension (the Buckets field) across columns (the original salary index data). By reshaping that I was able to create a Buckets field in the secondary, then generate a table calc on that.

Jonathan

• ###### 3. Re: Create weighted average using data blended connections

Mr Drummey - thank you for your assistance!  I will look this over as soon as possible and try to implement into my production model.  I'm sure I'll have questions.  :-)

• ###### 4. Re: Create weighted average using data blended connections

You're welcome!

• ###### 5. Re: Create weighted average using data blended connections

Mr. Drummey, I am in awe of your Tableau skills.  I was able to translate your file into my production version and from a math perspective, I get exactly what I was expecting.  This is most excellent.  THANK YOU THANK YOU THANK YOU!!!

• ###### 6. Re: Create weighted average using data blended connections

Your packaged workbook and problem description were quite helpful in generating a solution, I'm glad it worked for you!

• ###### 7. Re: Create weighted average using data blended connections

Based on what you provided, I was able to create an integrated view that shows the individual index values as well as the weighted average value and some descriptive information.

Question though - Each work location code has an associated city and state.  If I were to filter by State or City, how would this impact the window average?  I can't have the weighted average changing even if I filter to look only at specific locations.  My thinking is that the weighted value shouldn't change as long as I keep work location and ERI bucket as dimensions on the level of detail shelf and that even if I look only at one work location, that's how the math is working anyways so it shouldn't impact anything.  Would you concur?

• ###### 8. Re: Create weighted average using data blended connections

I don't think this should be a problem, the calc partitions on the work location code and even if State or City were in the view they would just be added to the partitioning.

Jonathan