Hi Peter, it is possible to do, but it seems to me it doesn't give good information; it seems like the unit cost as is is correct. However, this is how you can achieve what you are trying to in Tableau.
it's an order of operations issue. First each measure gets summed, and then they get divided. This carries into the Grand Total: It is dividing the Grand Total Sums 1240/285.
We can change that, but we'll have more flexibility in using calculations if we join your data rather than having it blended as it is now. I'll go over the join real quick in case you aren't familiar with it (hopefully your data sources will allow a join).
I went to Data, New Data Connection:
And connected to the Units spreadsheet:
Then, I used the "Add" link in the left panel to bring in the Costs spreadsheet. Using the "Add" link lets you join rather than blend.
Don't use New Data Source from the Data menu or the database icon. Then you'll have to blend.
Using the Add, you'll be able to bring Costs into the Data window and set up your join. I used an inner join, but if it's possible for the Units spreadsheet to have Date/Process that isn't in the Costs spreadsheet (or vice versa), and you want to bring the unmatched records in, then you'll need to choose one of the other joins.
Ok, now I'll set up the worksheet the way you had it before, and add in another formula:
Unit Costs: Divide Then Sum --- Sum([Costs]/[Units])
Unit Costs: Sum Then Divide --- Sum([Costs])/Sum([Units])
The [Unit Costs: Divide Then Sum], you have to be careful with calculations like these - if you have other dimensions that bring more granularity to the data, be very careful when you use it that the results you get are what you expect . It has to divide at the level of granularity you wish it to.
Ok, I hope that helps.
All the best,