6 Replies Latest reply on Jun 16, 2018 10:59 AM by William Graham

# grouped column with cumulative lines on double y-axes

I'm trying to create a graph that shows monthly totals for planned and actual in a grouped column - not difficult - I got that.

AND I need to overlay the monthly cumulative amounts of planned and actual to the second y-axis.

I can get half-way there in Tableau - either grouped columns OR the two cumulative lines, but not both on one graph.

This is how I did it in Excel.

The graphic shows what I need (ignoring formatting), and I've attached an spreadsheet with the raw data.

The table in columns A-D are pseudo-data but representative of the native data.

Columns G-K are representative of pivot table output.

For this exercise I copy/pasted values for graphing simplicity as I don't want to accumulate past the current fiscal month.

Any ideas?

• ###### 2. Re: grouped column with cumulative lines on double y-axes

Hari

Thank you for the reply, however this doesn't get me to where I need to be.  I need cumulative lines overlaid upon monthly columns and I need to terminate the cumulative actual line at the last month, not plateau out to the end of the year.

• ###### 3. Re: grouped column with cumulative lines on double y-axes

You have discrete values for the scenario which make the actual and plan bars be adjacent to each other. Due to this, you cannot have continuous lines for the cumulative numbers. As for the cumulative actual line to stop at the last month, that can be achieved as shown below.

1 of 1 people found this helpful
• ###### 4. Re: grouped column with cumulative lines on double y-axes

I figured out that is a use a Size (from a calculated field) determined by Scenario

Size = IIF([Scenario]="Plan",-.02,.02)

Then accumulate using

Cumulative Amounts = IIF(SUM([Amount])>0,RUNNING_SUM(SUM([Amount])),NULL)

I can then do a dual axis

• ###### 5. Re: grouped column with cumulative lines on double y-axes

Nice solution. Can you please share your workbook so that I can learn what additional magic you have done?

• ###### 6. Re: grouped column with cumulative lines on double y-axes

Yes sir.

I forgot to attach it to the previous message.

It's there now: Book1.twbx

Thanks again.