1

Stacked and non stacked chart together

   Hi

 

I have four measures, one being premium, and then an set of data of claims split by type, I want to see the premium as one colour in its own bar, and then the 3 claim types stacked together against the premium, is this possible?

 

Many Thanks


Nick

1 reply

null
    • Senior Director of Product
    • Ian_Macdonald
    • 2 yrs ago
    • Reported - view

    Hi Nick,

    Bit of a tricky one this, as while we can create composite charts of Line & Column, the stacked column chart is a variation of the column chart and we cannot currently create composite charts from the same chart family. Please use the Product Ideas forum to suggest this as an enhancement.

    We can, however work around this as follows.

    The colours in  a stacked measure bar is driven by the measures, four in your case. I also need two categories to allocate the measures into, Claims and Premiums. I then create four calculated measures, based on the real ones and allocate them to the two Categories, such that if it is allocated to the Claims categories, set the value to that Claim measure and the Premium value to zero. The opposite for Premiums. When stacked by Claims and Premiums, the Premium values will not contribute to the Claims category and the Claims measures will not contribute to the Premiums category.

    In my example below, I have four measures, Sales, Expenses, Cost and Overhead. I want a single column for Sales and the others stacked as Costs.

    First we have to create the "holding categories", Sales and Cost. You can use any other dimension to do this, it doesn't matter which really. In my case I have a dimension called Channel. I am going to create a member called Cost, set to null, in the channel dimension:

    Create another Member called Sales in the same way

    Then create analogues of your four measures. We want all the outgoing measures, Cost, Expenses and Overhead allocated to the Cost Member to retain their value while the in-coming Measures, Sales, is set to zero for the Cost. Then for the Sales Member, the opposite. Sales retains its value, all the others are set to zero.

    A few things to note here:

    • Use the "Current Member" option for the Channel dimension selection
    • Use the Caption() function to retrieve the Member name.
    • Testing the name of the current member (Channel calculated members, Sales and Costs) to allocate the Measure Value.
    • The Data Point refers to the Measure concerned but also defines the "All" option for channel, as otherwise there will be no Measure values for "Sales" and "Costs". This will be ok as long as you don't want to use channel as a filter etc. this is why one chooses a not commonly used dimension.
    • If you do want to use the hosting dimension to filter, use parameters on that dimension and select those in the data point instead. 

    Now simply construct the chart using a stacked measure column chart, using the dimension holding the "Sales" and "Cost" members as categories (plus others if needed) with those two members selected:

    Hope that helps.

    Ian

Content aside

  • Status Answered
  • 1 Likes
  • 2 yrs agoLast active
  • 1Replies
  • 38Views
  • 4 Following