7 Replies Latest reply on Nov 27, 2014 7:06 AM by Tableau kumar

# Grand Total with Table Calculation in Blended Data

Hi guys,

I have two data sources, first one has the sales records contains UPC, quantity sold, category name. But my costs are from a separate data source that is a list including unit cost by UPC. I blended these two data sources in Tableau and wanted to get a aggregated costs by item/category by using quantity from source 1 multiply unit cost in source 2. The calculated field of 'Total Costs' is created based on this logic. When I drag it into view, the costs showed up by UPC perfectly except grand total (Sheet 1) . The summation of column 'Total Costs' is apparently \$1034 while grand total shows \$761.

I did a table calculation with 'window_sum' in Sheet 2 to get cogs by category, the grand total worked fine, When I drag the same calculation in Sheet 1, the grand total goes wrong again... I don't even understand how this \$761 is calculated, anyone could help me from here/

Thanks,

Suzie

• ###### 1. Re: Grand Total with Table Calculation in Blended Data

Hi Suzie,

I don't have an answer yet and my break is over, but I can at least hopefully point a little further just now. It has to do with the average calculation and summary rows:

If you use average in a group, it calculates the average for that group. However in the summary row, it doesn't do the average of all averages or the sum of all averages but does the average for everything in the table.

So your costs have 28 records (not counting the NULL) and total unit cost of 406, making an average cost of 14.5.

Your grand total uses 14.5 * total quantity of 52.5, which results in 761.25. That is where your number comes from.

However... I assume that in the grand total row, you need the sum of all total costs per group, but that means you need a sum of averages. Not sure how to do that.

Hope it helps you further though.

Dana

• ###### 2. Re: Grand Total with Table Calculation in Blended Data

Hi Suzie,

I have the same problem as you. Did you find a solution for it?

Bests,

Daniel

• ###### 3. Re: Grand Total with Table Calculation in Blended Data

Hi Daniel,

I never get this resolved in Tableau, the issue is precisely described by Dana. We ended up changing our query to handle costs average... If you find any smart solution on this, please inform me

Thanks,

Suzie

• ###### 4. Re: Grand Total with Table Calculation in Blended Data

Hi,

I have the same question too.

Do you know how to resolve it?

Thanks

Marina

• ###### 5. Re: Grand Total with Table Calculation in Blended Data

Go through below link, it has 2 data sources & Calculations  using 2 Data Sources.

https://public.tableausoftware.com/views/Calculationsfrom2DataSources/Sheet1?:embed=y&:display_count=no

Best Regards

Laxman Kumar

• ###### 6. Re: Grand Total with Table Calculation in Blended Data

Thanks for the reply,

I have a question- where is the grand total?

If I want to see the average CAL1 and sum CAL2?

there is no business logic to a sum of CAL1