Product Tour
forums
Use these forums to ask questions and discuss Tableau.
How do I use the MIN aggregation to remove the double counting?
I'm having trouble with Tableau over-representing some sales data. I have two tables:
ORDERS
- ID
- TOTAL_COST
ORDER_DETAILS
- ID
- ORDER_ID
- AVAILABILITY_ID
- QUANTITY
I've created the Foreign Key relationship between the two tables. Each Order record has one or more Order Details records. If I perform a SUM(TOTAL_COST) I see an inflated value. If I view the underlying data, I can see the problem: each Order row -- including the TOTAL_COST -- is selected with every Order Detail row for which it contains. So the SUM(TOTAL_COST) is actually equal to SUM(TOTAL_COST * (number or ORDER_DETAILS joined rows)). Digging in the documentation, I found the following:
"When you connect to multiple tables you are essentially connecting to a denormalized view of the data source. This means that all queries are run against all tables and it is possible for some measures to be over counted. For example, suppose you have an employees table and an orders table. However you keep the employee salary measure in the orders table. The salary will be counted for each order the employee made. Use the MIN aggregation to remove the double counting."
How do I use the MIN aggregation to remove the double counting?
Subscribe to the comments on "How do I use the MIN aggregation to remove the double counting?"
Comments
The MIN() function (or MAX) function will simply give you the value of the order for that order, regardless of the number of records because it selects one of the values (the least or the greatest, which happen to be the same). This only works at the ORDERS level of detail and below. If you try to aggregate above that level of detail, you will get the value of the order with the least total cost.
Are you connecting to a database or Excel workbooks?
Any chance of pulling the cost column from the data source for the individual line items in the ORDER_DETAILS? Or, any chance of pulling the line item count into the orders table (then you can divide the total_cost by the number of line items to prevent double counting.