12 Replies Latest reply on Oct 26, 2018 3:22 AM by Kylian Pattje

URGENT: Grand totals are WRONG!

I'm very new to Tableau and need to prepare some reports.

I don't know if I can explain what I'm doing, so I've attached the workbook.

In all sheets, grand total of column is wrong. (It's the same number of 3rd row).

Why is this wrong? How do I fix it?

• 1. Re: URGENT: Grand totals are WRONG!

--Shawn

Oopse. Sorry I forgot the link, it's there now. And my first cup of coffee just arrived...

• 2. Re: URGENT: Grand totals are WRONG!

Umm.. what?

EDIT: Thanks for the link. Fixed the post.

• 3. Re: URGENT: Grand totals are WRONG!

Thanks for posting a packaged workbook.

The reason why the Grand Totals are coming out as they are is that Grand Totals are computed as the measure at a higher level of aggregation. In the worksheet you posted, every return_order has a LensKart value, so the COUNTD(return_order) returns 1872 for LensKart and does the same in the Grand Total.

You just said the "grand total of column is wrong", you didn't specify exactly how you wanted the grand total aggregated. See http://community.tableau.com/docs/DOC-5069/ for more information on grand totals and some links to different solutions.

1 of 1 people found this helpful
• 4. Re: URGENT: Grand totals are WRONG!

I'm sorry for being so vague. But I simply wanted the sums of all rows and columns. So you're saying Tableau will show the maximum of the values as grand totals? How do I set it to compute actual grand totals of rows and columns?

• 5. Re: Re: URGENT: Grand totals are WRONG!

What you say are "actual grand totals" is a specific sort of secondary computation, namely an aggregation (a sum) of your aggregate calculation (countd). That's not how Tableau currently works. What Tableau does is use the original aggregate (countd) at a higher level. If you SUM is your original aggregate, then Tableau sums across the column/row. If it's MIN or MAX, then Tableau computes the MIN or MAX across the column or row. For COUNTD, Tableau is computing COUNTD() across the entire column or row.

In SQL terms, here's how this plays out. In the Returns MTD worksheet, the computation for each cell looks something like:

SELECT COUNTD(return_order_id)

FROM [Returns MTD]

GROUP BY return_type, kart

In the Row Grand totals (the columna at the right), the computation looks like:

SELECT COUNTD(return_order_id)

FROM [Returns MTD]

GROUP BY Kart

This is not a sum, it just works out like that because of the data. In the Column Grand totals (the row at the bottom), the computation looks like:

SELECT COUNTD(return_order_id)

FROM [Returns MTD]

GROUP BY return_type

Again, this is not an aggregate (a sum) of the COUNTD aggregate, it's the COUNTD computation at a higher level (as if return_type was not in the view). What you're looking for for the Column grand total is something like:

SELECT SUM( SELECT COUNTD(return_order_id) FROM [Returns MTD] GROUP BY return_type)

A quick way to make Tableau do that is to use the 2nd technique from http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/, using duplicates of both Kart and return_type on the level of detail. I set this up in the attached.

1 of 1 people found this helpful
• 6. Re: Re: Re: URGENT: Grand totals are WRONG!

Hi Jonathan

I tried doing what you did, but I'm getting something really strange. Please see the attached workbook (Sheet "Returns MTD")

Thanks

EDIT: I checked with the data team. They are telling me that the grand total is correct, but the row for "LensKart" is the same as grand total, which obviously is wrong.

EDIT 2: When I change COUNTD to COUNT, everything works. However, it's not the number I want. I want count of unique [return_order_id] and [return_id]

• 7. Re: Re: Re: URGENT: Grand totals are WRONG!

You did not set the Compute Using for the Revised Return MTD calculation, it needs to be an Advanced... Compute Using on  Kart (copy) and return_type (copy):

2 of 2 people found this helpful
• 8. Re: Re: Re: URGENT: Grand totals are WRONG!

I cannot find Compute Using anywhere!

• 10. Re: URGENT: Grand totals are WRONG!

Right click on the table calculation's pill and choose Edit table calculation… For a set of tutorials on table calculations, see http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/.

1 of 1 people found this helpful
• 11. Re: URGENT: Grand totals are WRONG!

Hi What if I have a Manufacturer  in the Columns as well I get the result only for 1st Manufacturer whereas my other Manufacturer columns reflect blank.

I used the formula

IF FIRST()==0 THEN WINDOW_SUM(([YTD _CY])) END

• 12. Re: URGENT: Grand totals are WRONG!

Did you exclude any values? I had similar problem calculating the difference between periods and grand total wasn't working as i wanted.

Rather than excluded i filtered and it worked out.

good luck,

Kylian