3 Replies Latest reply on May 12, 2016 2:33 PM by BI BLZ

# Calculated fields, level of detail... and blended data sources

Now 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.

• ###### 1. Re: Calculated fields, level of detail... and blended data sources

Have you tried using unions in Tableau Desktop 9.3? It sounds to me like your issue is occurring because of the blending.

• ###### 2. Re: Calculated fields, level of detail... and blended data sources

Yes, the issue occurs because of blending, but it's not a case of table union. These are not similar tables to be united, each table has different KPIs (as opposed to similar KPIs for different segments) and we need to combine those different pieces of data to calculate profitability.

(but no, we have not yet upgraded to Tableau 9.3)

• ###### 3. Re: Calculated fields, level of detail... and blended data sources

So.... apparently Tableau 10 solves this issue by allowing joining tables from different data sources. Eager to try it.