Merge separate lines into one single line whilst retaining separated stacked columns
For some context, the 'Gross Incurred Claim' measure refers to the stacked columns whilst the 'Rolling Average 12 Gross Incurred Claim' refers to the four separate dashed splines.
I was hoping to keep the stacked columns as per the attachment (separated by the 'Claim Type' representing different colours) whilst at the same time having one *single* spline that takes into consideration ALL claim types when calculating the rolling average. Not sure how I can do this whilst the Claim Type is kept in the Color field.
Any help would be greatly appreciated!
Unfortunately, using Claim Type in the Color Drop Zone will always split the measures into the different values of Claim Type, so you will always get a line for each claim Type showing the rolling average for that Claim Type and not the total Claims as needed.
However, we can work around this by creating a measure for each Claim Type plus an aggregated measure for all Claim Types, then stack the Claim Type Measures and show the total Claim Rolling Average as a line. Here's how:
In my example, I am using Promotion Category instead of Claims and my measure is Sales.
Create a Measure for each Promotion Category using Sales:
And one for the Aggregate of all three:
Then simply construct your chart using the four measures. Months for the categories of the chart (filtered by Year in this case) and the individual measures for each Promotion Category as a Stacked Column, then the Aggregate as a Line. Then Show the Aggregate as Rolling Average, in this case using 3 months:
In your case obviously substitute Claim Type for Promotion Category and construct a Measure for each Claim Type plus the Aggregate of Claim Types for the rolling average line.
Hope that helps.