Clustered Stacked Column Chart: Segmented Analysis

[CPIBecklon]

Hi! I want to create one chart that would work with data with the following columns:

1st Column: Time from A to C

2nd Column: Time from A to B

3rd Column: Time from B to C

For my visual, I want to break the chart out into 4 categories accross the X-axis: Mean, Segmented Mean, 95th%, and Segmented 95th%. The Y-axis will merely be the accumulating time.

The Mean and the 95th% categories would present data from 1st column.

The Segmented Mean and Segmented 95th% would present data from the 2nd and 3rd column, but stacked.

It would look something like this:

The problem that I have encountered thus far is that I can't get the Mean and 95th% to be pulled out as their own category. Instead, the Mean gets lumped in with the Segmented Mean Values. I have tried to create columns with labels (Mean, Seg Mean, 95th, Seg 95th), but that does not help since all of the data is being used. I have tried to create measures and drag the measures in, but that does not seem to help.

Is there a way to make this work...without creating 4 separate charts?

By the way, I realize that I could specifically use a waterfall chart, but I am hoping not to do that.

Thank you in advance for any help!

[CPIBecklon · Accepted solution]

I am happy to report that I solved this. The way I approached it involved much work. Maybe there is a better way. Please share if you find a way.

1. I took my primary query with the calculations (e.g. A to C, A to B, and B to C) and duplicated (or referenced) them about 4 times, naming each of the duplicates as follows: Mean, Mean Segmented, Percentile, Percentile Segmented.

2. For each of the queries, I created a Category Field and named them accordingly (Mean, Mean Segmented, Percentile, Percentile Segmented).

3. For the Mean and Percentile Queries, I ripped out the calculations "A to B" and "B to C". Also, in each query, I relabeled the "A to B" calculation. For example, in the Mean Query, I labeled the column as "A to B Mean" and in the Percentile query, I renamed it as "A to B Percentile.

4. For the queries Mean Segmented and Percentile Segmented, I ripped out the "A to C" calculation column. Then, I renamed the remaining calculation columns, similar to the method I used in Step 3.

5. I duplicated the Mean Query and then renamed it "Combo".

6. I appended the following queries to the Combo Query: Mean Segmented, Percentile, Percentile Segmented.

7. Close and Save.

8. I located the following in the fields pane: "A to C Percentile", "A to B Percentile", and "B to C Percentile". For each of these, I created a measure to calculate the 95th percentile.

9. In the Visualization Pane, I chose stacked column chart.

10. I drug "Category" from the Combo query into the AXIS field.

11. I drug the following fields into the VALUE field and set them to AVERAGES: A to C Mean, A to B Mean, B to C Mean.

12. I drug the following MEASUREMENT fields into the VALUE field: A to C percentile, A to B percentile, and B to C percentile.

I hope this helps!