Continue from Part 1, there is one aggregation within Tableau that is commonly acted on a dimension label rather than measure label. Tableau does offer a page of explanation (link), though without some examples, it is hard to associate its meanings.
Note: Tim from TIL has an excellent video on YouTube for the topic, feel free to check that out as well.
For our example, let's go to the Superstore in Tableau and drag Manufacturer and Customer Name onto the row shelf.
Next we will filter our Manufacturer to only Avaya and Socket for demonstration purpose.
If we just create a calculated field, such as ATTR([Customer Name]) and bring into the view, we will see it does nothing.
If you read the description within the function explanation
Returns the value of the expression if it has a single value for all rows. Otherwise returns an asterisk. Null values are ignored.
So ATTR() function is used as a check for unique value and it is a very simple logic in fact
IF MIN ([dimension]) = MAX ([dimension]) THEN MIN ([dimension]) ELSE "*" END
When the at the desired granularity for the view, if a dimension gives unique value, then MIN() should MAX(), otherwise "*" is used to indicate not. In the table above, each customer has its own row, then at each customer level, the value is unique, hence values are returned and there is no *.
Note 1: When used on text, MIN() return the first value in the list, usually by alphabetical order. MAX() returns last value.
Note 2: MIN() and MAX() can be applied to (continuous) measure, hence ATTR() function can be applied as well. But when a number has decimal part and floating, it will cause issue when compare with =.
Let's quickly create two new calculated field just to check.
Nothing special is happening, as MIN() and MAX() returns the same value for each name, hence the ATTR() will return a value as noted before.
Note: All calculated field has AGG() applied to them. The default for dimension label is MIN().
BUT what happens if we don't let value to be broken down by Customer Name? (i.e. decrease granularity of the calculation)
(Similarly create one for MAX())
Now it is easy to see the first customer (alphabetically by first name) is Alan and last customer is Vivek for Avaya. Socket has only one customer, so it is used as a check.
So far so good, everything as expected. Now let's remove Customer Name from the view
Note: We can also right-click on Customer Name and change it from Dimension to Attribute to get the same result.
Now we can see what the * does here, it tells us there are more than 1 customer within Avaya, while there is only 1 customer for Socket. As much as it is frustrating to deal with when it appears, it is a good indication the something is breakdown the aggregation within the view to finer granularity than desired.
Once identified the dimension label that is breaking the calculation, we can then handle it with either table calculation or LOD or just filtered view.
No more panic when the * shows up any more, Tableau use ATTR() function a lot by default, but we are prepared now to manage it better.
Looking for more guides, tips and tricks in Tableau or Alteryx? Go check out the other blog posts from the Data School.