5 Replies Latest reply on Aug 28, 2013 11:24 AM by Aaron Clancy

# Split horiz. bar chart into two "columns?"

I have a horizontal bar chart with 25 rows. Its total height is a bit too high for my taste, so I'd like to split it into two side-by-side graphs. I want the top 13 rows, based on the Number of Records, on the left, and the bottom 12 on the right.

I DON'T want to create two separate worksheets, because then the row size would only be relative to that filtered worksheet. ie, the top of the bottom 12 would appear as large as the top of the top 13. So I need the scaling to be unified.

I've tried dual axis, with two Number of Records columns, to show what I want. However, both rows function the same, use the same filter, and occupy the same side of the graph. I've attached an example with Superstore data of what I have so far. I need two "Category" rows, one with the top data, the other with the bottom. And simply duplicating the Category dimension didn't work.

Is there a way to do this?

• ###### 1. Re: Split horiz. bar chart into two "columns?"

This is one way to do it (Workbook attached)

• ###### 2. Re: Split horiz. bar chart into two "columns?"

Hey Aaron--this is neat.  Can you explain the X and Y axis calcs, and your general process for developing this?

Thanks!

• ###### 3. Re: Split horiz. bar chart into two "columns?"

Yes, very clever.

--Shawn

• ###### 4. Re: Split horiz. bar chart into two "columns?"

From what I can tell, the X axis on the Column shelf separates the two columns:

IF INDEX()<=ROUND(SIZE()/2) THEN 1 ELSE 2 END

Computed w/ State

So, if the number of the State row (index) is less than the total # of State rows of/2, AKA, if it's in the first half, then it gets a 1. If it's greater, AKA in the second half, it gets a 2. This creates the two column values. They can be any number, as long as they're both different. Without them, each row of 2 states would be squished together.

The Y axis on the Rows shelf creates the rows for each state:

IF INDEX()<=ROUND(SIZE()/2) THEN INDEX() ELSE INDEX() - ROUND(SIZE()/2) END

Like the first calculation, if the number of the State row is in the first half, then it uses its index as the row value. If it's in the second half, then it subtracts the halfway row # from the index, and uses THAT as the row value. This aligns it with a State from the first half. Without this, there would be 2 columns of 12-13 states each, squished together.

All you have to do beyond that is sort the State by whatever value.

• ###### 5. Re: Split horiz. bar chart into two "columns?"

Yes Michael is correct.  Make sure to compute both table calcs at the level of the dimension that you are showing and have that dimension on the text mark detail with right alignment.

Sent from my iPhone