This week at the data school we are delivering a blog post each day, showing a 2 minute tip.

Here we go! My kick-off for this Tableu Tip Week is about getting the totals with count distinct!

Let’s first connect Tableau to our favorite Superstore Dataset, and build a very simple view of the number of unique Sub-Categories of products sold in each state.

It should look like this:

Now, we just want to put a “Grand Total” after our values, to see how many unique Sub-Categories of products we sold.

Therefore, we go to Analysis, and select “Grand Total”. Like this:

Unfortunately, the result is pretty weird!

Seventeen?! How could the total be seventeen!

Moreover, in the status bar we can see that the SUM of CNTD of Sub-Categories is 656, so we expected to get that result.

The problem here is that Tableau is not computing Totals as aggregates, but just as other values, at the lowest level of granularity.

Basically, instead of “summing the sums”, it is just returning the total number of unique subcategories, which is seventeen, as we can see using the Index() function.

There are different ways to overcome this issue, and getting the expected result.

The easiest one – that coach Andy showed to me last week – is coming back to the Analysis Menu, and set Tableau to compute Totals using Sum!

In this way, we are telling Tableau to compute the Totals aggregating the values in the view a second time, using the Sum, and the result is correct.

Here you can find the Tableau “knowledge base” article about this tip.

I decided to write about it because I found the example in that article too simplistic to be explanatory, and the proposed workaround more laborious than the one here explained.

I hope this has been useful and clear, and I invite you to come back tomorrow for a new tip!