Follow the Three simple steps below to have the Stacked combination chart ready.

1. Drag and drop a Stacked Column Chart, and map the Rows 2, 3, 4, 5  to it as Series.

Add one series to it and keep Map the series name to Cell A6. Keep its values blank.
Behavior -> Scale -> set the max limit to the Cell “B7” and min as “0” .

Behavior -> Scale ->Divisions -> Number of Divisions -> 4

Behavior -> Common -> Check the – Ignore Blank cells in “Values”.

Appearance -> Layout -> uncheck the “Chart Background”

Appearance -> Text -> uncheck the legend , make both the axis (horizontal axis labels and vertical Axis Labels) color same
as in the background. In the image below its been kept white as the Background color is white here.

Make sure the Chart title Subtitles are blank.

B7 = TRUNC(MAX(B6:M6)+(10*MAX(B6:M6)/100),0)

// This sets the max limit of Y axis labels to 10% of the maximum Value.

One of the best practices you can say as it reduces the Chart’s empty space.

Stacked Chart and data.JPG

axis colors.JPG

2. Drag and drop a Line Chart, and map the Rows 6 ( “Target” )  to it as Series.

Follow the Same thing as in the stacked column Chart. Make sure the Line chart is on the top of Stacked Column Chart.

Line Chart and Data.JPG

3. Copy Paste the Stacked Column chart over the Line chart. Re-size it as shown in the below image and make the “legends Visible”

Stacked Column -2 .JPG

Drawback : The only Drawback it has is ( Appearance -> Layout -> “Enable hide/show chart series at run time
wont work here”. )

.xlf file of this has been attached the with an extension of .txt , it can be viewed by changing the file extension to
.xlf after downloading.

Thanks & Regards,

Sara

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

    1. James Batchelor

      You can do Clustered, Stacked charts with similar technique..just have to play with X axis offset to make 2 sets of stacks line up nicely…and be sure the Y max is globally set for both charts so the bars compare correctly.

      jim

      (0) 
  1. Durgamadhab Mishra

    Nice document Sara , I had this requirement in some times back and it took a lot of time to figure out the trick.. Now this can save a lot of time for a lot of people..

    (0) 
    1. Sara G Iyer Post author

      Yeah Durgamadhab,

      Even we had this requirement and also that I had seen a few discussions which required the same , so have posted it.

      Regards,

      Sara

      (0) 
  2. hari krishna

    Hi Sara,

                        I had done something similar to this a little while back. I had to use to stacked bar charts and a line chart. Most of the steps which have done are similar to mine, but the X-axis and Y-axis labels look little bit faded, I am attaching the link. For one stacked bar and line chart I put the X and Y axis labels in white color which is the color of my back ground but for one stacked bar I put blue. This looks little bit faded, due to other labels which are in white. Could please share your thoughts how to solve this.

    https://drive.google.com/file/d/0B6uAGK2zFLORbjN2NjFTYzV0cW8/edit?usp=sharing

    Thanks&Regards,

    Krishna

    (0) 
    1. Sara G Iyer Post author

      The chart which has white colored labels has to be sent to back, in the sense it should be behind the chart with blue colored labels. Here it looks like you have two stacked column charts, and one line chart, make the line chart to appear in the front, that will solve the issue.

      Regards,

      Sara

      (0) 
  3. Anandkumar KS

    Hi Sara,

    Basically, I like the documentation but after your presentation I learned something better than before.

    The way of presentation is good.

    Also, I have started my work in preparing the documentation and it will help me lot.

    Thanks

    Anand Kumar KS

    (0) 
  4. Mohan k

    Hi Sara,

    I have a requirement like in 100% stacked column chart values should populate upto certain percentage with some color and remaining empty should populate with other color(out of 100%) either in BI launch pad or dashboards.

    Thank you in advance.

    Regards,

    Mohan.

    (0) 
  5. C C

    Hi Sara,

    Excellent blog. Would a similar combination chart be possible in Dashboards 4.2, where we compare Quantities (Stacked Column) with Prices (line s)? This would also require Dual axis.

    Thanks,

    Chris

    (0) 

Leave a Reply