7 Replies Latest reply on Feb 24, 2017 12:37 PM by m jk

regarding totals and subtotals , need help ?

hi,

trying to understand the concept of total and subtotal with regards to average or sum.

version 10.0

sheet 8

for this data

Order Date Order Date Order Date Order Date
Product Category 2010 2011 2012 2013
Furniture 5,973 9,428 10,844 2,706
Office Supplies 2,836 422 632 3,423
Technology 254 4,362 2,392 2,695
Grand Total 2,910 3,619 3,273 3,160

avg (sales) for last column is showing 3160 , but 2706+3423+2695 =8824/3 =2941 but showing as 3160

sheet 6:

and for this data
result for avg sales
orderdate orderdate orderdate
Sno 2010 2011 2012
CCC 300.0 400.0 500.0
XXX 100.0 100.0 100.0
YYY 200.0 200.0 200.0
Grand Total 200.0 233.3 266.7

last column : 500+100+200=800 and 800/3 =266.7

same scenario but grand totals are different ?  why  avg sales are different.

can some one explain here clearly how it is calculating at  database level ?

as per this website , Customizing Grand Totals – Part 1 | Drawing with Numbers

Jonathan Drummey

• 1. Re: regarding totals and subtotals , need help ?

Hi,

I just wrote a long-ish response on the blog February 23, 2017 at 10:49 pm, if that doesn't answer your question please let me know. The key difference is with regards to the grain of the data and the vizLOD, in the Sno data set you posted the grain of the data and vizLOD are the same, whereas in Superstore the grain of the data is at the order item level and the vizLOD is at a much coarser level (Product Category & Year).

Jonathan

• 2. Re: regarding totals and subtotals , need help ?

Hi,

I have checked your Sheet 6, the grand totals are Avg across the row. So it looks ok.

Sheet 8

Corrected this one to calculate average.

Hope it Helps!!!

Thanks

Deepak

• 3. Re: regarding totals and subtotals , need help ?

sorry see this attachment sheet8

Deepak Rai

• 4. Re: regarding totals and subtotals , need help ?

So, are you looking for this?

• 5. Re: regarding totals and subtotals , need help ?

Jonathan Drummey

are the points are correct or not  ?

based on what i read from the blog

1)the automatic behaviour is wrong

2) we have an option to ovrecome this  using twopass key

Analysis->Totals->Total All Using->Average menu (this sets the aggregation for all measures in the view):

which gives 2941 average of the rows

• 6. Re: regarding totals and subtotals , need help ?

are the points are correct or not  ?

based on what i read from the blog

1)the automatic behaviour is wrong

I'd argue that instead of right/wrong we need to be asking "Does the behavior match our expectations or not?" In the case of SUM(), MIN(), and MAX() the automatic (default) grand totals behavior is computing the sum of all values in the given context, the minimum across all values, etc. In the case of AVG(), MEDIAN(), PERCENTILE(), etc. the automatic behavior is computing the weighted (overall)  average, median, etc. across all values.

So the conflict of Tableau's behavior vs. our expectations comes when our initial expectation is that we want to see the Grand Total be something like the average of the displayed averages rather than the (default) weighted average. Unfortunately many times that is the case for new users and it's the source of a lot of confusion.

2) we have an option to ovrecome this  using twopass key

Analysis->Totals->Total All Using->Average menu (this sets the aggregation for all measures in the view):

which gives 2941 average of the rows

There are two ways we can turn on two pass totals. One is from the Analysis menu as you found, the other is from right-clicking on each measure pill itself in the view, where we also have the option to entirely turn off grand totals if we want.

Jonathan

2 of 2 people found this helpful
• 7. Re: regarding totals and subtotals , need help ?

thankyou sir