2 Replies Latest reply on Sep 15, 2017 7:53 AM by Thomas Rones

# Calculated Field Issues --- SUM, FIXED, VIEW SUM

I was practicing for the Tableau 10 Certified Professional and on the first question I got some weird results that show that I really don't understand how calculated fields operate.

It is my understanding that the dimensions determine level of detail in the view

...I think I just figured it out. Will show my resolution at the end.

Here was my original problem

I am trying to get profit as a % of sales .... Profit/Sales.

[Profit]/[Sales] was giving me incorrect values...but

SUM([Profit])/SUM(Sales)

{Fixed [State or Province]: SUM([Profit])/SUM([Sales])}

both gave me the expected values.

So is my thinking here correct?:

[Measure]/[Measure] does a row by row operation. Then when you drag the pill out the aggregation is applied up to the level of detail in the view.

And Aggregation([Measure])/Aggregation([Measure]) ; Aggregates to the level of detail in the view and then does the division.

----------------------------------------------

... I think I must just throw a small bit of calculate-able in head test data in and test it out that way.

----------------------------------------------

Superstore just has random values???

....But when I dis-aggregate measures there are still crazy values... Is the superstore dataset just like this?

How could profit be 100,000s% of Sales?

I guess it can be:

...So now I am back at square one. Just gonna use some test data.

• ###### 1. Re: Calculated Field Issues --- SUM, FIXED, VIEW SUM

hi Thomas,

So yes in your first version

[Profit]/[Sales]

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

1 of 1 people found this helpful
• ###### 2. Re: Calculated Field Issues --- SUM, FIXED, VIEW SUM

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