Stacked Column Chart with Line Combo

[rileybloke]

Hello,

I have a stacked column chart showing "spend" values from a measure across a time dimension for each spend type.

I would like to add a target line which is taken from another measure (budget), but when I add this measure to the chart, the chart changes from a stacked to a normal column chart.

Any ideas, thanks

Phil

 

[ImkeF]

Hi Phil,
click on one of the data rows - right mouse click - select "Change series Chart type".
In the bottom area you should be able to allocate the chart types to each individual data row/measure - at least in my Excel 2013 this sticks.
Imke

 

[rileybloke]

Thanks, done it, but now as this is a stacked column a stacked column consists of approx 3 different values (actual spend), now I have added my (budget) and changed to a line, it has put 3 different lines.
I prefer to have 1 line running along the axis as this is the total budget for all 3 different values.

In summary my objective is to create a stacked column chart showing the 3 value amounts for each month along the axis. But then show the total budget of the 3 values as line.
My measures are calculating correctly, just having trouble presenting it on the graph.

I tried creating a transparent line chart with just my budget measure and placing it on top of the stacked column chart but it doesn't line up very well.

Any ideas? thanks again

 

[ImkeF]

Everything I've experienced with Pivot Charts ended up in limitations compared to normal charts - leaving me frustrated

Therefore now I'm using cube formula when it comes to charting. There you just create your table with just what you need for your chart and: done
In your case: Individual lines for your actuals and sum only for your Budget.

If you don't want to go this path: Stick with my example and choose a stacked line. This will give you one line that you want (the highest for your Budget figure) and edit the 2 other indivicually: don't show lines or transparent colour.

 

[kitjosh1050]

What is a good reference for learning how to use a cube formula in a non-pivot chart. I'm trying to do something similar.

I have a variable number of specimen categories, between 1 and 8 depending on filter, that make up the stacked column series. The x-axis is a pathologist. I want to add a line that is the overall average of the pathologist for that period. I'm at a point of giving up at this point. I can't seem to find a worthwhile solution.

I'm using Excel 2010 btw. I very much like the 2013 set-up, but it hasn't been implemented widely at my org yet (although we do own it )

 

[ImkeF]

No problem with 2010 - no change in cubefunctions since 2007

Have a look at my post here: https://social.msdn.microsoft.com/F...forexcel#f715d585-9741-42d8-834d-53d474d3150c
also contains link to example file.

Peter Myers Video is excellent: https://channel9.msdn.com/Events/TechEd/NewZealand/2013/DBI304 (just ignore the limitations towards dynamic reports - can be done as you will see in my examples)
also:
Cooking With CUBEs | Excel Do, Dynamic Does
Using Excel CUBE Functions with PowerPivot « PowerPivotPro

In your case:
Drag in as much as you need for your report from your PivotModel, incl. the filters as slicers if you want to keep it interactive.
Design in a way that it resembles your desired report as much as possible.
Convert to cubeformula and add additional columns/fields as you need it (i.e. overall average, if you don't have this in your model), delete what you don't need and create your chart on top.