Stacked area chart legend issues

[jthomson]

Hi,

Having some troubles getting a visual to display as I like. Here's my data model, or at least the relevant parts of it, as it may be relevant to the measures used:

NEWREN is a list of insurance policies which I need to relate to everything to create global filters. DCL is a list of claims on those policies, while GEP is a list of earnings based on those policies - tables are linked on a policy number. Date table does what it says on the tin, and is linked to DCL and GEP by file opened date and earnings date respectively.

I need to create a measure which basically compares the number of claims received to the amount of years of policies which have already expired. Here's the relevant measures:

Frequency = divide([Claim Count],[CumulativeVYE])

Claim Count = CALCULATE(countrows(dcl),filter(ALLSELECTED(dcl),DCL[File Opened]<max(DateTable2[Date])))

CumulativeVYE = CALCULATE(countrows(GEP)/365,filter(all(DateTable2[Date]),DateTable2[Date]<=max(DateTable2[Date])),USERELATIONSHIP(GEP[GEPDate],DateTable2[Date]))

These measures all work individually fine, and they give me a graph which works as intended.

The issue comes when I want to split the frequency measure down by fields in the DCL table, for example by accident type. Right now, the overall frequency is, say, 12.5%. If I put the accident type field into "filters on this page" and pick one from there, it will correctly filter - it will show road traffic accidents at 7.5%, and anything else as the correct amounts combining up to the remaining 5%. However, if instead I put the accident type field as a legend in a stacked area chart, I get the following result:

Rather than filling the area by the level of each type, it's duplicating the overall area over and over. Any ideas as to why this might be happening and how to modify the formulas to make this work?

[MFelix]

Hi @jthomson ,

It's difficult to pin point the error, I see that you have an inactive relationship between two tables however I don't know if that influences the result since the measures arent explicit asbout that.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[jthomson · Accepted solution]

I've just ended up making individual measures for each way I want to split it out and it works, not ideal but it'll have to do. Cannot share the pbix

[MFelix]

Don't forget to mark the correct answer.