1 2 Previous Next 16 Replies Latest reply on Aug 4, 2016 3:22 AM by Kwan Pichyangkul

# Grand Total using calculated field with ATTR()

Hi All,

I'm using a calculated field that multiply the value with its currency conversion rate. Using this calculation

SUM([Sales])*ATTR([Currency (Test total)].[Rate])

When using grand total, the total shows nothing. I want to see the sum of converted Sales and Target. I've tried multiple ways to solve this but can't seems to get it to work. Could you please help?

Attached is the sample workbook.

• ###### 1. Re: Grand Total using calculated field with ATTR()

Hi Kwan,

Sales after Conversion

sum([Sales])*AVG([Currency (Test total)].[Rate])

Target after Conversion

SUM([Target])*AVG([Currency (Test total)].[Rate])

Find the attached result.

Thanks and Regards,

Ashish Chaudhari

• ###### 2. Re: Grand Total using calculated field with ATTR()

Hi Kwan,

Please find the attached file for the reference.

-Ashish

• ###### 3. Re: Grand Total using calculated field with ATTR()

Hi Kwan,

There is a lot of discussion around this issue of grand totals, much of it summarised here:

Why Your Grand Total or Subtotal Isn't Working as Expected

In your example, sum([Sales])*attr([Currency (Test total)].[Rate]), the attr() section will return 4 different items (as you have 4 rates in your blended datasource) so when tableau tries to work out the total of  sum([Sales])*attr([Currency (Test total)].[Rate]) it doesn't know which of the rates to apply (as there are multiple ones) so returns nothing.

If you change it to sum([Sales])*AVG([Currency (Test total)].[Rate]) then you get a total figure (THE WRONG FIGURE!) because you're asking tableau to give the total of one figure multiplied by another single figure (the average of the 4 rates), not 4 separate rates as before.

I'm sorry this isn't a solution but hopefully you can find one in the link above as it's a very comprehensive run down of hoe totals work in tableau.

Steve

• ###### 4. Re: Grand Total using calculated field with ATTR()

Ashish, if you select all figures in the sales after conversion column, they add up to 8531, using Avg() the result is 8884 which is wrong because taking the average of all 4 rates and multiplying it by the total is not the same as applying each rate to the relevant sales amount then adding them together.

• ###### 5. Re: Grand Total using calculated field with ATTR()

Hi Ashish and Steve,

Steve is correct. I believe your code is applying the average of all of the conversion rates and multiply to the sum of Sales and Target, which results in incorrect grand total.

Regards,

Kwan

• ###### 6. Re: Grand Total using calculated field with ATTR()

Hi Steve,

Thank you for your help. I've checked that page but still struggling to get the result I'm after.

Regards,

Kwan

• ###### 7. Re: Grand Total using calculated field with ATTR()

If your Currency and Budget are in the same spreadsheet you could left join the currency sheet to the budget sheet so that rate is returned per row:

Then your sales after conversion could be a simple Sales * Rate and the totals work:

I've attached a workbook with this solution, hope it works for you.

Steve

• ###### 8. Re: Grand Total using calculated field with ATTR()

Hi Steve.

Sales and Target are actually comes from Custom SQL. Currency is from Excel worksheet. So I can't left join them directly like your example.

Regards,

Kwan

• ###### 9. Re: Grand Total using calculated field with ATTR()

Hi Kwan & Steve,

Steve is absolutely right. Good catch.

Since we have Currency rate is at country level, can at what conversion rate grand totals should be calculated?

-Ashish

• ###### 10. Re: Grand Total using calculated field with ATTR()

That's a shame!

Is there no way to add the currency table to your SQL database? that way you could left join it to your custom SQL?

in tableau v10 they're allowing cross database joins which will alleviate this issue:

• ###### 11. Re: Grand Total using calculated field with ATTR()

Also, one more slightly manual option, is to have the 4 currencies as parameters and your sales with conversion field set as:

CASE [Currency]

WHEN 'USD' THEN Sales * [USD parameter]

WHEN 'AUS' THEN Sales * [AUS parameter]

.....

etc.

Obviously that's only useful if you have a few currencies and don't mind updating them manually

• ###### 12. Re: Grand Total using calculated field with ATTR()

Hi Steve,

There's a bunch of reports that are facing this issue so if possible, I'd like to avoid updating the SQL.

But if that's the only way then there's no choice..

Thank you,

Kwan

• ###### 13. Re: Grand Total using calculated field with ATTR()

It means there is no other solution that hard coding the same and working in the active database.

-Ashish

• ###### 14. Re: Grand Total using calculated field with ATTR()

Hi Mark, Any idea about how to tackle this one?

-Ashish

1 2 Previous Next