2 Replies Latest reply on May 25, 2012 11:41 AM by Gérard Garreau

# column totals

Hi,

here is my (twofold problem). I have data on students help that look like :

student name and id, faculty, department, program, help_program, amount

on the other hand, I have a already aggregate  table of all the students in each program:

faculty, department, program, number of students.

I want a sheet that display:

faculty, department, program, sum(amount), number of students receiving help ( i use cound), total number of students in the program, % students reciving help

secondly, is there anyway to "overide" the total. In my case, I have a sheel that display average, but I would like to get the total at the bottom to display the sum of the average

Thanks

Gérard Garreau

Reporting and Data Analyst

Concordia University

• ###### 1. Re: column totals

Hello Gérard,

I can give an answer to your first question, however I'm making some assumptions about the data that could be wrong. If that's the case, then posting a packaged workbook with some sample data would make determining the exact calculations needed much easier.

The total number of students in the program could be calculated by SUM([Number of Records]) or TOTAL(SUM([Number of Records])), you'd use the latter if Student Name or ID are in the Level of Detail in the view (which they might be if you are using COUNTD). Then the % of students receiving help would be pretty straightforward (number of students receiving help)/total students.

As for the total, Tableau's grand totals are not a truly "visual" total of what's in the view, they are a separate calculation performed at the appropriate higher level of aggregation, and as such can end up with different results. There are two options to deal with this. The recommended approach is to create a second view that has the totals you want, then put the two views together in a dashboard. An alternative approach that requires a good understanding of table calculations is to follow the outline I laid out here:

Finally, here's an Idea I created to ask for totals that are calculated over what's visible in the view, vote it up if you think it's a good one: http://community.tableau.com/ideas/1232

Cheers,

Jonathan

• ###### 2. Re: column totals

Jonathan, Thanks for the answer. I get around my problem by counting distinct the student id on a calculated field :

if [GL_AMOUNT] > 0  then [STUDENT_ID] else null end

I then COUNTD the new field. The total number of registered student is COUNTD (STUDENT_ID)

My probelm with "total" is also a count distinct so that it look as if the summation are wrong. They are NOT but you always have somebody n the audience who is happy to point out that the total are WRONG! and that the entire thing must be ....