6 Replies Latest reply on Apr 25, 2016 11:51 PM by SUNDO KIM

# Give me a correct calculation for total.

Column AColumn BColumn C
USA3.074,9941.25
Total5.2412,253

5.24

I caculate the Column C just like the next.

SUM([A]) * (SUM([B]) / Total (SUM[B])

5.24 * (12,253/12,253) = 5.24

But I just want to get 2.53(= 1.25 + 1.28)

What should I do?

• ###### 1. Re: Give me a correct calculation for total.

Hi Sundo,

Don't do any calc like yours. Select analysis-total -column grand totals.

Thanks

sankar

• ###### 2. Re: Give me a correct calculation for total.

Howdy Sundo,

Is the Total field coming from your database? If so lets exclude that and put it as a context filter, because we do not want to use it at all. To make it a context Filter Right-Click the field on your filters shelf and then choose 'add to context'. Alternatively we can remove it as a datasource/extract filter as well.

Even if you follow Sankar's suggestion you will get the 'funky' values this is because how table calculations work. The default grand totals work without regard to the aliases they ran under. Your example of 5.24 * ( 12,253/12,253) = 5.24 is spot on.

So instead lets create a Level of Detail Calculation- for short we call these LODs. You can find more information about them here. http://interworks.co.uk/blog/tableau-level-detail-calculated-fields-v9/.

Our new calculation for Column C is

SUM([Column A])

*

(SUM([Column B])/MIN({FIXED : SUM([Column B])}))

Then Go to the Analysis Menu and Add Grand totals for Columns. Then repeat this but this time change the total using and switch it to SUM.

What this calculation does is very similar to what you had before but the FIXED function works different than the total function. The total looks at the table that's returned from the database while the FIXED function will look at the specific level of detail in this case none was defined so the entire database and sum for column B before. It then returns this result from the entire database in every row. Because its the same result for everything we had to use some level of aggregation in order to aggregate your other fields and use them both in maths at the same time. So by wrapping it in MIN we get the value aggregated (its the same value so max works as well).

Cheers!

Carl Slifer

InterWorks

2 of 2 people found this helpful
• ###### 3. Re: Give me a correct calculation for total.

Hi Sundo,

Please try this one. As Sankar explained you don't need to use calculation for this one. This functions are already built in.

Thanks and Regards,

Ashish Chaudhari

• ###### 4. Re: Give me a correct calculation for total.

my total came from there already. thanks.