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.
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.
not only another mind but the best mind imho. Thanks Jonathan.