Splitting a measure into two parts for a stacked bar chart

[benrickard]

Hi there, I have a measure (number of insurance policies sold over time) that I want to split into two parts, new and renewed and then show these on a stacked bar chart. We have a separate variable (Type) that indicates if a policy is New or Renewed. How can I split the measure of "Policy Volume" into Policy_Volume_New and Policy_Volume_Renewed for a stacked bar chart? Or is there a better way to do it?  Cheers! Ben

[Vegar]

I would try to add the Type as a dimension to your bar chart. Then in the properties pane under presentation choose to present the bar as stacked.

[Vegar]

The other approach is to split the expression into two expressions like you are suggesting.

Create two expressions like this.

Sum({<Type={'Renewed'}>}[Policy Volume])

Sum({<Type={'New'}>}[Policy Volume])

[benrickard]

Hi there,
Thanks heaps for your quick replies. This second approach is the one I think will work best, but I get an error when trying to use it. I have adapted your sample to create a Measure, as below, but get the message "error in expression":
Sum({}[Application Volume])
"TransactionType" is a field name where the "Renewal" entry appears.
"Application Volume" is "master item" where the expression is: Count([applyID])
Can you help decode this error?
FYI, the other expression will need to be "all other transactionType that do not = renewal"

[Vegar]

I see two issues with your expression. 

1. You can't reference a master measure like the in an custom measure.

2. You can't do nested aggregations, like sum(count(...)), in a measure. If nesting is necessary then you need to aggregate between the two, like sum(aggr(count()))

In your case it could be enough to do like this:

Count({Your set}[applyID])

[benrickard]

Thanks for that, I think I am getting closer, but as a beginner I am still not sure how to make this work. I have got this now:
Count({[transactionType={'Renewal'}]}[applyID])
But it does not return any numbers.

[Vegar · Accepted solution]

@benrickard Try to adjust the expression like this:

Count({<[transactionType]={'Renewal'}>}[applyID])

[benrickard]

Thank you very much Vegar, that seems to have worked. Just a quick one, how to I express the same but for all the TransactionTypes that do not = 'Renewal'? 

[Vegar]

Try this expression:

Count({<[transactionType]-={'Renewal'}>}[applyID])

[benrickard]

That's worked too, much appreciated!