How to show project health changes over time, by manager?

[jshull]

Hello! 

I've got a little challenge here. First, here's a pic of the line graph I'm working with. This viz compares the average health of each manager's project portfolio. Projects are simply labeled Green/Yellow/Red to indicate their health. In order to make this line graph-friendly, I did some basic power query to make Green=3, Yellow=2, and Red=1. This is all connected to a massive .csv, not a live database. 

The data: example below. There's about 100k rows of this, one row per week of data, for thousands of projects. I only put three "health metrics" in here - Overall, Financial, and Customer Happiness - in reality I have a dozen of these to monitor. This example should be fine for discussion though.

Project ID	Manager	Date	Overall Status	Financial Status	Customer Happiness Status
123456	Trevor	10/26/20	Green	Green	Green
123456	Trevor	10/19/20	Yellow	Green	Yellow
123456	Trevor	10/12/20	Yellow	Green	Yellow
9876	Lisa	10/26/20	Red	Red	Green
9876	Lisa	10/19/20	Yellow	Yellow	Green
9876	Lisa	10/12/20	Green	Green	Green

The challenge: I want to enable managers to glance at the line graph, see downwards trends, then find what specific projects changed health to create that trend. Managers should be able to answer the question "what projects brought my average health rating down here?"

• This isn't as simple as creating a table that only shows "Red" projects for that manager - we need to see what projects changed their health status from green to yellow/red in the selected time period...and then exactly which health metric(s) changed.
• Example: Lisa notices a downwards trend in her average project health ratings in early September. She slices the date range to reflect that time period, and looks at another viz on the report page to see: three of her projects changed to "Red" in the Financial health area. Now Lisa can go investigate those projects, with an eye towards finances. 

My questions on this are pretty simple: 

1. How might this be done? 
2. What's the best/most intuitive way to display the results for non-techy managers?

Thanks a million in advance!

[v-kelly-msft]

Hi @jshull ,

According to the sample data you provided,I cant figure out how to get the result in your example:as for Lisa,it should be a upwards trend in the project,the status in December(December 10) is showing green.

Could you provide more sample data with expected output to make your requirement more clearer?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!