8 Replies Latest reply on Jul 13, 2018 2:43 AM by Sankarmagesh Rajan

# Grand total  - help needed

Hi Friends,

I have a requirement, as shown in below screenshot.

Currently, Tableau desktop tool provides Grand total for measure values available on the mark shelf. But what i need is as  -  Grand total according to 'First 6 months' for  (2017+2018) and 'Next 6 months' for  (2017+2018), as shown below highlighted in green color.

Is it possible to do the same in a single sheet/report?

Appreciate all help..

Thanks

Reshma

• ###### 1. Re: Grand total  - help needed

Hi Keerthi,

It may be possible, but we'd need the sample workbook with dummy data so that we can review and help you build this.

Thanks,

Mavis

• ###### 2. Re: Grand total  - help needed

attached twbx file

• ###### 3. Re: Grand total  - help needed

Hi Keerthi,

Is the below what you're looking for?

To do this, you do a union of the table onto itself:

So you have double of everything at this point.

However now you can create the calculation where you can find the totals per year, then the grand total:

Please see the attached workbook for an example.

Thanks,

Mavis

• ###### 4. Re: Grand total  - help needed

Thanks Mavis

Solutions looks very good for me... Thanks you again..

but cannot we do the same without using union/join, i mean with single datasource?

• ###### 5. Re: Grand total  - help needed

Hi Keerthi,

Unfortunately I don't believe so.

Thanks,

Mavis

• ###### 6. Re: Grand total  - help needed

Thanks Mavis...

• ###### 7. Re: Grand total  - help needed

Hi All,

For educational purposes, here it is using bins and data densification.  I wouldn’t recommend using this method in a production workbook unless you are highly comfortable with table calculations (I'm not). I would personally use the method Mavis demonstrated.

Regards,

Gerardo

• ###### 8. Re: Grand total  - help needed

Hi Keerthi,

Yes, we can do this single data source with union

create custom sql

SELECT ['Sheet1 (Book1)\$'].[Amt] AS [Amt],

['Sheet1 (Book1)\$'].[Customer Name] AS [Customer Name],

['Sheet1 (Book1)\$'].[Distinct count items] AS [Distinct count items],

['Sheet1 (Book1)\$'].[Months Desc] AS [Months Desc],

['Sheet1 (Book1)\$'].[Year] AS [Year],

'Data' as Grandtotal

FROM ['Sheet1 (Book1)\$']

union

SELECT ['Sheet1 (Book1)\$'].[Amt] AS [Amt],

['Sheet1 (Book1)\$'].[Customer Name] AS [Customer Name],

['Sheet1 (Book1)\$'].[Distinct count items] AS [Distinct count items],

['Sheet1 (Book1)\$'].[Months Desc] AS [Months Desc],

['Sheet1 (Book1)\$'].[Year] AS [Year],

'Total' as Grandtotal

FROM ['Sheet1 (Book1)\$']

Create calculation as

if [Grandtotal]='Data' then str([Year])

else 'Total'

end

Create view like below.

Thanks

sankar