A mistake I made when I first started using tableau was just using non-aggregated fields to do ratio calculations. I did not understand the way Tableau was calculating my data and therefore had no clue that the data that was then being displayed in my canvas was completely wrong. This led to incorrect analysis, which led to incorrect conclusions.
The problem was, I didn't even realise that everything was wrong. It looked ok to me. It was only when we learnt about the difference between row level calculation and a field calculation that I realised my mistake.
To really understand the difference, Andy Kriebel used the example of calculating profit ratio using the Superstore dataset. So lets say that we want to find the profit ratio for each of the categories: furniture, office supplies and technology:
Old me probably would have opened up a new calculated field and typed in "[profit]/[sales]". She would have gotten this result:
She would have realised that those numbers don't make sense. Profit ratio can't be above 100%. To combat this, she may have taken an average:
She would be happy as the numbers make sense and probably call it a day.
DO NOT DO THIS!
Lets look at this algebraically with a 3 record data
What I want is to add up my all of my sales, add up my all of my profit and then do [total profit]/[total sales]:
BUT, what the calculation [profit]/[sales] is doing, is dividing the value for profit by the value for sales for each individual row of data. It is then adding the results together. For example, [profit]/[sales] will produce column three:
So when it is dragged into view and automatically aggregated to SUM , what Tableau is doing is this:
For those who love maths as much as I do, this can be written as:
NOTE:
They are not the same thing. When we take the average instead of the sum of our [profit]/[sales] profit ratio calculation, we are almost taking an average of an average.
To correctly compute the profit ratio, you would need to sum the sales and sum the profits then divide. In Tableau, the calculation will look like this:
And will produce this when moved onto the canvas:
Much better!