Forum Discussion
Tracking Month on Month Project Status
Hi,
I am a PowerBI novice and trying to recreate a table which I have in excel that shows the status of a group of projects from one month to the next. I've tried to summarise this below.
I have tried appending the two monthly tables but I can't work out how to group each months data separately in a single Power BI output. At the moment, my output table is capturing the project's latest status across both months, when this may have changed.
Any help would be appreciated (I'd also appreciate really straightforward explanations!)
| January | |||
| Project | Status | Output | |
| A | Feasibility | 40 | |
| B | Planning | 100 | |
| C | Feasibility | 50 | |
| D | Pre-Planning | 75 | |
| February | |||
| Project | Status | Output | |
| A | Pre-Planning | 50 | |
| B | Construction | 100 | |
| C | Feasibility | 50 | |
| D | Planning | 75 | |
| Desired Output | |||
| Project Status | Output in Feb | Output in Jan | Monthly Variance |
| Feasibility | 50 | 90 | -40 |
| Pre-Planning | 50 | 75 | -25 |
| Planning | 75 | 100 | -25 |
| Construction | 100 | - | 100 |
| TOTAL | 275 | 265 | 10 |
Hi GJUDGE ,
You could specify the column to group and the desired output in Power Query Editor.
Then in ‘Home’ tab, Merges Queries>>Merge Queries as new.
Finally, create a column performing calculations on the Power BI Desktop.
desired output = Merge1[Output] - Merge1[January.Count]If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards
Community Support Team _ Polly
2 Replies
GJUDGE , if they are two tables, create two common tables, project and status and join with both tables and analyze the data together , you can use measure from both table and use them in visual against common dimension
refer if needed
https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19
Hi GJUDGE ,
You could specify the column to group and the desired output in Power Query Editor.
Then in ‘Home’ tab, Merges Queries>>Merge Queries as new.
Finally, create a column performing calculations on the Power BI Desktop.
desired output = Merge1[Output] - Merge1[January.Count]If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards
Community Support Team _ Polly
