4 Replies Latest reply on Jan 12, 2017 10:49 AM by Jonathan Drummey

Finding Average Based on 2 Worksheets

Hello,

I'm currently using Tableau 10.0.1 and am needing help with finding a way to obtain the average of "Table 1 Average" and "Table 2 Average".

I have 3 separate data sources: "RVUs", "Collections", and "Compensation". Percentiles from "RVUs" and "Collections" then correspond to Percentiles in "Compensation" and the output is the salary broken down by the survey it was gathered from. Table 1 is displaying salaries that are based on State, Job, RVUs, and the percentile that is based on Amount of RVUs. Table 2 follows the same concept but is based off Collections instead of RVUs. I currently have filters where you select State, Job, the amount of RVUs, and the amount of Collections that you are wanting to see the salaries for.

From what I can tell, there is not a way to create a calculated field that will give me the average of the two worksheets but I am wanting to know if anyone knows of a workaround to this problem. Thank you!

• 1. Re: Finding Average Based on 2 Worksheets

Hello Maureen,

The issue is more complicated because these two sheets utilize different primary data sources.  I checked out the views provided in the workbook (Thank you very much for sharing) and I do not think the desired avg of the two different avgs is possible here with the way the data is structured.  I do see these are excel worksheets though.  Is that how your data is stored?   I ask because this may be easier to address by going back to the data setup and determining if we can create a single data source and then apply calculations to the data to come up with the salary values for each of the two conditions and then avg those values in a single data source.  As it stands, the two values obtained are independent and can not be combined in a way I know of here.

Maybe my chiming in will also bring a few other minds to task on this but knowing where how this data is stored is probably the key to coming up with a solution that will work for you over the long haul.

Patrick

• 2. Re: Finding Average Based on 2 Worksheets

I'll be another mind on this... I took a look and all 4 sources are unions with the same 3 worksheet names. The two Comp sources have the same dimensions, the RVUs sheet adds a join to the State to Region Crosswalk. The Table 2 source also has the same fields  as RVUs plus a Collections dimension.

I didn't look fully, it appears that the dimension values are the same. So instead of having 4 separate sources and trying to do data blends I think a set of left joins would work, and assuming that I'd probably do the following:

Use the RVUs as the original set of tables.

Left join in the two compensation tables.

Then for the Table 2 source I'd probably use a data blend because that has an additional grain of the collection and use a Dimension filter to pick a single Collection value at a time.

Then a single view could be built out to get the averages per survey and then table calculations used to get the averages across the surveys and a nested table calculation to get the average of average.

Jonathan

• 3. Re: Finding Average Based on 2 Worksheets

not only another mind but the best mind imho.   Thanks Jonathan.

• 4. Re: Finding Average Based on 2 Worksheets

Aww, thanks!!