In the following example, you'll add a target for the quantity in each Category of Sample - Superstore. The Target table contains two columns, Category and Target, as you can see in this screenshot:
For this example, download the Superstore with Target Excel file from my blog, book.ladataviz.com, in the Chapter 10: An introduction to calculations section. Here's the direct link: https://ladataviz.com/wp-content/uploads/2018/09/Superstore-with-Target.xls. This Excel file contains two sheets, Orders and Target.
Let's add a target to our orders and deduplicate thanks to Fixed:
- Open Tableau and connect to the Superstore with Target Excel file.
- Create a Join between the two tables. As you can see, the Target value is duplicated:
- In a Worksheet, add Target in text. As you can see, the value is way above the expected sum of targets.
- Create a Calculated Field, name it Target - fixed , and write the following calculation: {FIXED [Category]:MIN([Target])}.
- Add the new calculation, Target - fixed, in text. You should see 37,000, the real value of the sum of the targets.
That's it! You can now use the Fixed calculation in all the Worksheets or other calculations; it'll always be calculated correctly.
As you can see, Fixed is a very useful function for removing duplicates, ignoring Filters, and specifying precisely the level of detail of a Measure. Always be careful to put the Filters in Context if you want them to have an impact on the Fixed calculation.