8 Replies Latest reply on Jul 3, 2018 8:41 AM by Tushar More

# Crosstab would like to have different grouping levels. One field grouped by month and the other grouped by year

Hi,

I  am new to tableau. I have a crosstab with the month and year for a financial dashboard

Month 1/2018   Month 2/2018   Month 3/2018          YTD Expenses    YTD Budget

Revenue            10                            20                    15                        45                         50

Expenses           1                              4                      5                         10                          20

I added the month/year to columns, category(revenue, expenses) to rows  and the monthlyexpenses to text to get the 1st 4 columns. I add grand total to get the YTDExpenses but how can I add the YTD Budget. I don't want this to be broken out by month but just  a YTDbudget that is 1 column. Due to the fact that I put the month/year in columns I can't get the ytd budget to show as YTD. It breaks it out by month which isn't what I want.

If anyone can help then that would be great thanks.

• ###### 1. Re: Crosstab would like to have different grouping levels. One field grouped by month and the other grouped by year

Hi Catherine

please attach sample workbook it will be much easier to explain formula. There are a few options.

Maybe in this discussion you find answer Calculated field for sum of all months

For Buget try calculation formula:

IF YEAR ([data column]) = 2018 THEN

[Budget column]

END

Anna

• ###### 2. Re: Crosstab would like to have different grouping levels. One field grouped by month and the other grouped by year

Hi Anna, I attached the sample tableau workbook. I tried adding the budget column as a calculated field but it still breaks it out by month. My goal is to add1 column to the end called budget amount that is YTD for the whole of 2018 and not to have the budget amount broken out by each month.  Thanks so much!

• ###### 3. Re: Crosstab would like to have different grouping levels. One field grouped by month and the other grouped by year

Catherine

I don't see workbook

Anna

• ###### 4. Re: Crosstab would like to have different grouping levels. One field grouped by month and the other grouped by year

Hi Catherine,

Something like this?

I created two worksheets and combine them in a dashboard.

~Tushar

• ###### 5. Re: Crosstab would like to have different grouping levels. One field grouped by month and the other grouped by year

Hi Anna,

I attached the file but it said that it was being moderated, so maybe it wasn't available immediately.. I just checked and it shows up now in my reply. Do you see it now?

• ###### 6. Re: Crosstab would like to have different grouping levels. One field grouped by month and the other grouped by year

Thank you Tushar! Is it possible to do it all on one sheet or do I have to create a Dashboard to accomplish this?

• ###### 7. Re: Crosstab would like to have different grouping levels. One field grouped by month and the other grouped by year

Please see file below Book11.twbx attachment.

• ###### 8. Re: Crosstab would like to have different grouping levels. One field grouped by month and the other grouped by year

Hi Catherine,

You can do this by unioning data to itself.

A couple of calculated fields.

~Tushar