I don't have an answer yet and my break is over, but I can at least hopefully point a little further just now. It has to do with the average calculation and summary rows:
If you use average in a group, it calculates the average for that group. However in the summary row, it doesn't do the average of all averages or the sum of all averages but does the average for everything in the table.
So your costs have 28 records (not counting the NULL) and total unit cost of 406, making an average cost of 14.5.
Your grand total uses 14.5 * total quantity of 52.5, which results in 761.25. That is where your number comes from.
However... I assume that in the grand total row, you need the sum of all total costs per group, but that means you need a sum of averages. Not sure how to do that.
Hope it helps you further though.
I have the same problem as you. Did you find a solution for it?
I never get this resolved in Tableau, the issue is precisely described by Dana. We ended up changing our query to handle costs average... If you find any smart solution on this, please inform me
I have the same question too.
Do you know how to resolve it?
Go through below link, it has 2 data sources & Calculations using 2 Data Sources.
I hope it will help you.
Thanks for the reply,
I have a question- where is the grand total?
If I want to see the average CAL1 and sum CAL2?
there is no business logic to a sum of CAL1