1 of 1 people found this helpful
So yes in your first version
This is being calculated at row level, and then when you bring it into the canvas the aggregation type is SUM. So it's SUMing up all the individual rows margin %...which is why the result is silly. You could go for AVG, not SUM and get sensible results, but they aren't correct as they are not weighted; If you look at the below example
The Average of 25% and 10% is 18%...but this is not the overall margin! If we SUM the Sales and SUM the Profit and then divide (the equivalent of SUM([Profit)/SUM([Sales]) in Tableau)...we get the right result.
Yes Superstore has some extreme Profits on a Transaction by Transaction basis, although so does most data...it's just that what we usually get from reporting systems is highly aggregated and so never see these low level nuances (if you do the SUM([Profit)/SUM([Sales]) and show it by Category...the numbers look much more like you are used to seeing).
I also thought you might find this useful
It's kind of how I think about calculations in Tableau
Hope that helps, and makes sense
Thanks. It's amazing how I spend so much time with Tableau, even giving client training courses, and I have overlooked this one little yet super important fact. ...Maybe that is why I scored so poorly on the calculations portion of the Qualified Associate exam...
Maybe from now on I'll just keep the log viewer up on my third screen whenever I am using Tableau