How to report out on Project Status Over Time (Line & Stacked Bar Output)

[Mrloler2]

Hello!

First off, I'm newer to PBI and though I have extensive Excel knowldge - I'm finding this to be a new ballgame. We're transitioning to PBI for our dashboard needs and I need to find a way to replicate some existing reporting in PBI (that I'm producing in Excel).

We have roughly ~100 Projects that each report a status of Green, Yellow or Red each month. I take the aggregate from those projects and consolidate in the following manner (rough sample). As you can see, I take the % data and run it through a combo chart that we track/trend over a running 6 month period. 

For the life of me, I'm having trouble finding out the best way to transform or organize the data in PBI that would allow for the same output (chart/graph). I've searched numerous forums and cannot find an explicit way to get this to work, despite days of trial and error, etc.

As a bonus question, i'm also wondering how I could position the data to allow me to drill down further into a graph like that to see WHICH projects have reported a certain status. For instance, if I wanted to see which 2 projects reported yellow in April 2023, etc.

I'm hoping a savant on this forum can help show me where I may be going wrong. Thank you so much!

	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23
Green	85	83	83	80	79	77
Red	0	0	1	1	0	1
Yellow	4	5	4	2	2	3
Total	89	88	88	83	81	81
	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23
Green	96%	94%	94%	96%	98%	95%
Red	0%	0%	1%	1%	0%	1%
Yellow	4%	6%	5%	2%	2%	4%
Total	89	88	88	83	81	81

[DataInsights]

@Mrloler2,

Is the first matrix example how the underlying data looks for each project? I'm guessing Project ID exists in the underlying data. I would think you could model the data to have columns Project ID, Status, and Date. This would allow you to aggregate and drill-down as desired.