8 Replies Latest reply on Jul 28, 2016 9:03 AM by Sam Fleisher

# Grand Total - Sum of count, countd, and percentage

Hi,

I have a data set that I am trying to create a work around of sorts for the total line.  The first column data points on each row are a countd.  For these, I want the total to sum those rows.  Next column is a count but also want total to sum.  Finally, third column divides second column by the first one and I need the total line to perform that same division and not sum or take average of that column.  Attached is an excel of what I'd like the final calculations to do.

Is there a way forwards or work around to have control over total line like I am describing?

• ###### 1. Re: Grand Total - Sum of count, countd, and percentage

Yes this is possible

Instead of A/B  use sum(A) / Sum(B) in tableau  where A and B represent your columns

This should give you the desired result.

If this does not work for you.

You can create the datasheet and the Total Line separately on different worksheets and place them on dashboard to imitate a single sheet.

Hope this helps let me know If you have any further questions

• ###### 2. Re: Grand Total - Sum of count, countd, and percentage

Hi kahlid!

Thanks for your response.  I can't do sum/sum because a = countd and b = count so it wont let me use aggregates on top of that.  Any other clever ideas? Very much appreciate your help!

• ###### 3. Re: Grand Total - Sum of count, countd, and percentage

apologies

I forgot that when thinking of it. Do you have a sample workbook for this data.

if Countd(A) / Count (B)  does not give you the total value

then you will need to create the total line on a separate sheet

• ###### 4. Re: Grand Total - Sum of count, countd, and percentage

Here is a screen shot of current workbook.

Can you explain what you mean by creating the total line on a different sheet?

• ###### 5. Re: Grand Total - Sum of count, countd, and percentage

So you would have

Countd(A)

Count(B)

Countd(A) /Count(B)

as measures on a second sheet without any dimensions to split the data

this will be your desired total value.

You can now place both sheets onto a dashboard to have it look like a single sheet.

If you have the raw data or a sample workbook I can help you achieve this.

Khalid

• ###### 6. Re: Grand Total - Sum of count, countd, and percentage

Here is the raw data.  Thank you!

Just to summarize, I need to be able to show subtotal for class a and class b and then company total for capacity (available seats) = countd(spot num), reservations =  (count of uid), and then percent filled = reservations / capacity.  The reason why (based on raw data) that capacity = countd of spot numbers is because we can possibly double book seats if something happens like a late cancel and some one on a waiting list takes that spot. So % full can be above 100%.

• ###### 7. Re: Grand Total - Sum of count, countd, and percentage

Hi Sam,

Have a look at dashboard 1 in this workbook.

Clicking on class a or class b will change the grand total to show values for selection.

I have left grand total generated by tableau on worksheet1 so you can compare these to grand totals coming from worksheet 2.

Let me know if you have any further questions on this.

Khalid

• ###### 8. Re: Grand Total - Sum of count, countd, and percentage

Thank you so so much!!  I sadly think the answer is I need to reorganize underlying data based on needs of how I need final dashboard to look.

Thanks again,
sam