Calculated fields, level of detail... and blended data sources
BI BLZ Mar 30, 2016 11:52 AMNow with a sample workbook...
Situation:
We need to determine Cost Of Goods Sold (COGS) for each day of sales (and month... and year... you get it). Seems pretty simple, right? Cost is given per product, and we know how many of each product goes into each order...
Oh, we're using Tableau 9.2 Desktop Pro.
Complication(s):
Data is spread across different tables in different databases (biggest motivation to use Tableau, in fact). As you have probably guessed, blending messes up the aggregated calculation for the total cost of each order, and consequently of each day.
As seen in attached sample workbook, we have:
Table one contains details for orders : order ID, when it was placed, which products it contained and in what volumes. (this is the primary data source)
Table two contains details for invoices: order ID, products contained and COGS for each line. (secondary data source blended by order ID and product ID)
However there's a delay from order to invoice (depending on several factors), therefore not all orders have invoices yet. Which is why we need to use current inventory cost as a proxy for COGS until the invoice is ready.
Table three contains details for inventory: product ID, warehouse, volume, total value and unit value (secondary data source blended by product ID)
The logic should be: if the order has been invoiced, use the cost from the invoice, otherwise calculate up from each product to determine cost of order, and then sum up all the orders to get the COGS for the day/month, etc.
The sample workbook includes the calculated fields that (we believe) will indicate how we should be calculating each step: averaging unit cost value in stock (when there is stock spread across more than one warehouse with different costs), multiplying by the volume in each order, selecting either this calculation or invoice value.
Line by line (per product) everything seems to work fine, but the aggregation to higher levels of detail (order, day, etc) is off:
1. Total COGS estimated for Order C should equal the sum of the COGS for each product ($111,1 instead of $78)  instead of summing up, Tableau calculates again on the total quantity of items and an overall unit cost (for the whole inventory)
2. Grand total for final COGS is not summing up any value from Order C (as it doesn't have an invoice, but the others have). It should be $259,6.
3. Subtotals for each day are for some reason equal to the grand total, summing the order(s) from the other day.
Can anyone out there help us find the solution? This is going to be a recurrent sort of issue for us here at BLZ.

COGS.twbx 39.3 KB