Hello Trevor

Is there any way you can anonymise the data and attach a packaged workbook to this thread? Or mock up the problem using the superstore data supplied with tableau? It'll be miles easier to see what's going on and assist you.

Thanks

Steve

Thanks Trevor, I'll see if I can figure it out!

Sorry Trevor, can I ask why you went for sales per customer to use in this calculation? from your original post you said you were looking to "sum all of the receipts by business function" and if you choose Sales as your measure, your calculation works:

Is there something in your data structure that requires you to create a calculated field like the Sales per customer and use that instead of summing the receipt values?

Steve

Sorry Trevor, I didn't make it very clear what I was doing.

For your example you used Sales per customer which is a calculated field - SUM(Sales)/COUNTD([Customer Name]). This will behave differently within a Level of Detail calculation to a simple SUM(Sales) would. I switched the sales per customer field with the SUM(Sales) which is why the numbers are so different.

I asked whether your real data involved a similar calculation to Sales per customer or whether it was a simple SUM because if it was the latter, the calculation should work {FIXED [Category]: SUM(Sales)}

The sum of everything in the red box = $724,000 and the sum of everything in the green box = $719,047 and Calculation 1 will remain that value whether you expand the hierarchy or not.

If this isn't working with your data then please let me know how the field you're currently trying to SUM is calculated (i.e. what the formula is) and I'll have another look tomorrow morning.

Apologies for the poor explanation, I've attached your workbook with the edit.

Steve

You're welcome, sorry I couldn't solve the problem.

Sounds like Excel would be the more appropriate application for a data table.  The benefit of this from your point of view is that you can use VBA to modify the individual cells as you programmatically see fit.  Tableau's specialty is visual charts.