This problem might not appear to be challenging at first. The first step is to calculate the average profit per customer. One way to do this is to create a FIXED LOD calculation.
We can then add this calculation to the view so that the reference line can access it. We’ll put “Average Profit per Customer” onto the Detail shelf. Now we can right-click on the profit axis and select “Add Reference Line”. We can set the value to our calculation and I am going to change the label to show the value.
If the parameter control is configured to show outliers, then you should have a reference line with a value of $361.16. Let’s see if the value of this line will change when we choose to hide outlier customers. Hmmm…something isn’t quite correct. The value changed to $336.44. Why is this happening?
Let’s consider what is happening step-by-step. We know that the “Include Outlier” parameter and the “Profit Outlier Filter” are working together to only show the customers according to our selection. When we choose to hide outliers, we see the number of marks decrease from 793 to 780. The filter is working correctly, as the outlier customers are no longer part of the visual. However, it appears that the “Average Profit per Customer” calculation is being computed after this filter as the value of the reference line has decreased.
This leads us to one of the most important topics of Tableau: the order of operations. Everything in Tableau happens in a particular sequence and this is summarized in the image below:
In the view, we have a measure filter as well as a reference line. Measure filters are towards the bottom of the order of operations. Reference lines are the very last thing to be computed! The “Profit Outlier Filter” is being executed first and removing the outlier customers from the view. Then the reference line is being computed based on the 780 remaining customers. We need to fine tune the order of these events in order to achieve the desired result. We would like the reference line to be computed before the outlier customers are removed from the view. How can we do this? We are going to use all three elements that fall below Measure Filters in the order of operations. These include Totals, Table Calculations and Reference Lines.
If you haven’t already read the solution for Master Challenge #5, you might not know that the sole purpose of table calculation filters is to remove previously computed marks from a view. We can “demote” the Measure Filter to a Table Calculation Filter. This will result in the filter being computed further down in the order of operations. To do this, we can make use of the LOOKUP table calculation. LOOKUP is a table calculation that looks up a value somewhere in the current partition according to a specified offset. This is frequently used when trying to calculate differences or percent differences. In our case, we don’t want to look up a different value. We simply would like to return the same value of the measure filter but convert it to a table calculation filter. To do this, we can create a new calculation as follows:
The offset of 0 tells Tableau to use the same value returned by the “Profit Outlier Filter” calculation. This calculation simply returns the same value, but with an important distinction. Since LOOKUP is a table calculation, the new calculation we just created is itself a table calculation. We have effectively demoted the filter so that it executes later in the order of operations.
Now we can remove the existing filter from the filter shelf and utilize the new table calculation. After you select a value of “Yes” you should see the following:
Let’s check on the reference line. Nothing has changed. It is still displaying an average profit per customer of $336.44. This is to be expected because although we demoted our filter from a measure filter to a table calculation filter, the filter is still being computed before the reference line. What we need to do now is to “promote” the calculation in the reference line. Let’s open up the reference line by editing it. We are currently using an aggregation of average. To force this to be computed before the table calculation filter, we can choose an aggregation of total. You’ll notice that Totals are located one step above Table Calculations in the order of operations. This means that the average profit per customer will be computed based on all 793 customers.
Now the value of the reference line will display an average profit of $361.16 per customer regardless if the user chooses to show or hide outlier customers.
There you have it. By thoroughly understanding the order of operations you will be able to fine tune the sequence in which filters and calculations are being executed.
There you have it. By thoroughly understanding the order of operations you will be able to fine tune the sequence in which filters and calculations are being executed. You can download the solution workbook here.
If you need help solving this Tableau challenge or any other assistance with Tableau, ask one of our Tableau Pros. If you’re interested in significantly improving your Tableau skills, we offer Tableau Training courses including a Tableau Master Class for advanced users.
ETL is the set of data preparation and integration processes by which data is extracted from numerous databases, applications, and systems; transformed as appropriate; and loaded into target systems – including, but not limited to, data warehouses, data marts and analytical applications.
Our team of experts have designed and implemented countless ETL projects. We employ the latest ETL methodologies and best practices using many industry-leading ETL tools. Our solutions are designed and tailored to your specific needs and situation.
Creating and managing an effective data warehouse is often critical for creating useful visuals. And the way data is stored is paramount. Tableau and similar tools will struggle if the data isn’t easy to access. Unilytics consultants can ensure the data architecture and design suits your business intelligence agenda.
Data on customers, suppliers, communities, and other sources arrives in various forms. Businesses use this information daily to make intelligent decisions across virtually all their functions - so ensuring it’s accurate is key. One way of doing this is through data enrichment.
As an Alteryx reseller and consulting partner we help you accelerate insights and improve decisions with this industry leading tool. You'll love the Alteryx self-service analytics platform for its ability to easily prep, blend, analyze, and publish your data using a repeatable workflow designed to save time and pain.
Ask us for a complimentary consultation - just leave your email!