forums

Use these forums to ask questions and discuss Tableau.

Calculations including Grand Totals

Posted November 1st, 2007 by Ricky Coppola
in Calculations

I have data that looks like this:


Category Value
Cat 1 2
Cat 2 3
Cat 3 5
Cat 4 7

I need to show the distance of each value from the average of all values...like this (Avg. is 4):

Category Value Distance
Cat 1 2 -3
Cat 2 3 -1
Cat 3 5 -1
Cat 4 7 -3

I have tried table calculations, but can't seem to get it. How can I achieve this ?

Add new comment

Comments

On November 1st, 2007 Erin Easter said:
Instead of doing the calculation, a good approach is to answer the question visually. Put Category on the Columns shelf and Value on the Rows. You will now have a nice bar chart. Then, on the Sum of Value axis, right click and add a Reference Line. Choose the average. Voila! If you really want the actual number, then change the aggregation from sum to average and create a table calculation that is the Percent of Total. That's as close as Tableau can currently come.
reply
On November 1st, 2007 Ricky Coppola said:

I have another question on this. I am getting different total amounts when i add a certain field to a table row but use all the same filter values. For example:

State Sales
SC200,000
TOTAL200,000

Then I want to break these SC sales down by Customer, so I drag Customer to the Rows shelf & get this:

StateCustomerSales
SCJoe25,000
SCBob100,000
SCBill25,000
SCDan45,000
TOTAL195,000

When I view the underlying data on both, the table including customer excluded 2 rows (for the same customer) that were included previously. These 2 rows make up the discrepency, but all of the filters are set the same.....why were they excluded ?

Love the tool....Thanks in advance !

reply
On November 1st, 2007 Erin Easter said:
There are lots of possible reasons. Most likely you have an aggregate filter that is being applied to the results. It doesn't come into play in the first table, but due to the smaller numbers in the second, it is eliminating a tuple (result row).
reply
Syndicate content Subscribe to the comments on "Calculations including Grand Totals"