I have a very basic multidimensional dataset: a default rate on annual cohorts, per week, per location. I'm using a standard star schema data model (first screenshot below). For this project, "location" and "college" are synonymous.
Based on the earliest cohort's starting date, I calculated out "FirstDayOfWeek" for each record. Each cohort is tracked over a period of several years. With a FirstDayOfWeek calculation, data points can occur on the same day. For an extremely simplified example, the 53rd week of the 2015 cohort and the 1st week of the 2016 cohort will have the same "FirstDayOfWeek". Some mocked-up data is shown in the second screenshot below. (I have seven total locations.)
The basic 2D line chart only permits a single field for Legend and Value--as such, I am currently averaging the default rate across all locations for each cohort year and plotting them against the date range. This outputs nicely when combined with tooltips to describe the week recorded for each cohort (third screenshot). The lines stop at the end of each cohort's reporting period. (2016 and 2017 are still active.)
What I'd like to do is have a separate line for each location in each cohort. Changing the legend from Cohort to Location gets me really close (visually) to what I want to accomplish, but as you can see in the fourth screenshot, the average is including all data points reported on a given date, including overlapping cohort data.
Any thoughts? If any of this is unclear, let me know. I could provide the PBIX file if needed but I would need to change some of the data elements.
Thanks in advance!
For the life of me I cannot edit my message due to some "invalid HTML" nonsense, so I just want to expand this thought:
... as you can see in the fourth screenshot, the average is including all data points reported on a given date, including overlapping cohort data. I want to avoid averaging overlapping data points for different cohorts.
Could you share me your pbix for better analysis?
You could upload your file to OneDrive and copy the link, then paste the link into the Url of Insert/Edit link.