7 Replies Latest reply on Aug 2, 2016 10:00 AM by Cheryl Morgan

# Totals

Good morning,

I cannot get the total that I need. I have attached an excel worksheet because our network will not let send the right data. I have place what I need in red. I have 'Buckets' for my data, and I need to calculate the total for all buckets, in this case it will be in a specific time frame. Then I need the percent, but that should be easy if I could just get  total.

I just hop it will let me attach the excel sheet.

Cheryl

• ###### 1. Re: Totals

Try this:

{FIXED [Time Frames]:SUM([January])}

Or if your data is actually better structured behind the scenes (i.e. flatter) then you could alter the formula to something along the lines of:

{FIXED [Time Frames],MONTH([Date]),YEAR([Date]):SUM([Measure])}

Obviously that formula would need to change to reflect your actual field names.

1 of 1 people found this helpful
• ###### 2. Re: Totals

Hi Cheryl,

Your data structure is not great for tableau. You will need to fix this either with a pivot within tableau (not the best) or from outside with an ETL tool.

You will want the following columns from a raw data structure in order to make best use of tableau

TimeFrames     Buckets   Date      Value

If the data is already in this form and you copied data from tableau and didn't explain that above then depending how your tableau is currently set up and I will imagine you have the following:

Columns: YEAR([Date]), MONTH([Date])

Rows: TimeFrames, Buckers

Text: SUM([Value])

Now what you need to do is Right Click the SUM([Value]) and quick table calculation, choose percent of total.

Then right click it once again and go to compute using and choose 'pane down'.  If for some reason you still need the TOTAL next to each one of these create a new calculated field. WINDOW_SUM(SUM([Value])) and drag that field from your measures pane onto the text table. You will notice that by doing this Measure Names was added to your columns shelf or rows shelf and measure values is now on your text shelf and there will be a measure values card immediately underneath your marks card. Right click the new measure on the measure values card (this window sum one) and change it to compute using pane down as well.

If this doesn't help a mockup of your data and the issue you're having inside tableau would be the most appropriate course of action for your to take next.

Best Regards,

Carl Slifer

InterWorks

1 of 1 people found this helpful
• ###### 3. Re: Totals

Hi Andrew,

An LOD is a bit out of place here as the functionality can be done with a table calculation. Using a table calculation instead would minimise the querying done to the the database and it would further complicate the issues of having to make calculations behave according to filters, etc.

LODs are a fantastic tool but they get overused quite a bit when a simpler solution exists.

Cheers,

Carl Slifer

InterWorks

• ###### 4. Re: Totals

Carl, agreed in some situations, perhaps not in others - difficult to say what is most approriate here as we don't know what the underlying data looks like. Based on the supplied Excel it appears in this case the table calculation would need to restart every month/year/time frame which makes it more complex.

As you know there are multiple ways to skin a cat in tableau, we don't have the necessary information in this case to say what is the best way to skin this one :-)

• ###### 5. Re: Totals

I apologize, I should have stated that my data is coming in as string values from SQL Server. Every time I try to convert I am getting an error. Sorry I am working on 2 things at once, and forgot the important part.

• ###### 6. Re: Totals

You are absolutely correct. Each month will have its own total. It is not cumulative. Each month is loaded into SQL. Also, the data is loaded as a string. I need to have the percentage equal to 100% for the month. for instance it will be 2%, 5%, 67%, 11%, 15% respectively. This gets for complicated way I have to add the hospital unit into to the mix, but I will deal with that later.

Thank all of you for your help, as you can tell I am very green at this. I read a couple of  articles (Customizing Grand Totals – Part 1 | Drawing with Numbers ) that made this seem as if I will not be able to do this.

• ###### 7. Re: Totals

The good news is I have the percentages working. I just cannot get the total to show.