How to use a FIXED Level of Detail (LOD) Expression to manage multiple Fact Tables in a Join in Tableau

In Tableau, when we connect to multiple tables and the join consists of more than one fact table, the output of this join may result in a dataset that has duplicate rows to that of one of the other fact tables. This is a common scenario when a single record from the first table might find multiple matching records from the second table, depending on the linking or joining columns. In these cases, the resulting dataset repeats the record from the first table the same number of times as the number of matching records from the second table.

For example:

Below is a sample set of records from Table A, ORDER_DETAILS. The unique key from this table is a combination of Order Number, Order Date and Item Code.

Below is a sample set of records from Table B, BILL_DETAILS. The unique key from this table is a combination of Order Number and Bill Number.

We want to create a report that will show each order with the total quantities of all items along with the number of bills generated for the respective order. For this purpose, the two tables must be joined to each other on the common column which is the Order Number.

Notice that for a single order from Table A: ORDER DETAILS, there are multiple bills in Table B, BILL DETAILS. For example, the order number CA-2013-152156 has two bills, BL-CA-2013-152156-1 and BL-CA-2013-152156-2.

The screenshot below shows that, when we join these two tables in Tableau, the Order Number column from both tables has been used to set a join between the two tables.

Below is the resulting output of the join.

Note: For order number CA-2013-152156, the quantity value 20 appears twice as there are two linking bill numbers. Similarly for other orders, there are multiple records in the resulting table.

Now we will create a report in Tableau showing the order numbers, order dates, item codes and total quantities.

For order number CA-2013-152156, the total quantity [SUM(Qty)]  is 50 (20 + 30)), however the total quantity is shown as 100 (40 + 60). This is due to duplicate records for this order, as shown below.

In such cases I have observed that, as a general tendency, the immediate impulse is to simply average the values in the Quantity column by dividing the sum of the values by the total number of records. However, as noted above, the total number values are not correct due to duplicate records.

To get the correct result, we can use Level of Detail (LOD) Expressions, which were introduced in Tableau 9.0 to provide a way to easily compute aggregations that are not at the level of detail of the visualization.

To solve the problem, we will use one of the LOD function that is “FIXED” to restrict the duplicate records while calculating the average of the values. The FIXED LOD calculation is constructed below.

In the above calculation, we average the quantities by the unique columns in Table A, ORDER_DETAILS. While calculating the average using the FIXED LOD expression, we actually go back to the database (the joined dataset) and pre-calculate the average at the level of Order Number, Order Date and the Item Code. Below are the results of this pre-calculation.

This Tableau Tip was written by:

Sourabh Dasgupta

Sourabh Dasgupta is the Director of Technology & BI at Corporate Renaissance Group India and is also the Product Manager of CRG’s in-house software products such as FlexABM and Cost Allocator. Over the past 14 years he has been involved as a technical expert in various projects and assignments related to implementation of Activity Based Costing and Business Intelligence and reporting systems in India as well as South Africa.

He is a one of the professional trainers for Tableau software and has conducted many trainings for the clients. He has been involved in designing and development of Tableau reports and dashboards, and was awarded with a Tableau Desktop 8 Qualified Associate certificate as well as a Tableau Server 8 Qualified Associate certificate.  He has successfully conducted trainings on the Tableau Software (Desktop and Server versions) for many clients, which included basic and advanced features of the Tableau Desktop and Tableau Server.

Sourabh has a Bachelor of Science degree (Computer Science, Physics, and Mathematics) from Nagpur University, India and a Diploma from the National Institute of Information Technology (NIIT), Pune, India. He also holds a Microsoft Certified Professional certificate for Designing and Implementing Desktop Applications with Microsoft Visual basic.

If you wish to accelerate your skills and empower your learning with hands-on education, or are seeking an Expert-on-Demand, reach out to Sourabh at sdasgupta@crgroup.com