Forum Discussion
How to add SPI Calculation with roll ups in Power BI- Project on the Web
Please note: this is for Project on the Web - not PWA/MSPO. I currently have a Power BI that is calculating SPI for me, but it is only considering each ROW. I am not sure how to tell Power BI whi...
- There isn't really a screen shot. I have a project on the web schedule imported into Power BI.
I have the start date, finish date, and percent complete for each task.
I have a top level task- which is the overall project summary. There are many tasks, many which have sub tasks under those. And some even go again more subtasks.
In Power BI, I have a column to figure the expected percent complete - based on start and end date. I then have a column to divide the actual percent complete, with the expected percent complete. This gives me my SPI. So on schedule, is 1.0 result.
The calculation is per row. However, I need to create a hierarchy so I can get the SPI for each "section" of the schedule. An SPI per row is a start, but it doesn't give an accurate picture of the overall project. As I get the summary tasks SPI included and the subtasks, so it is counting those twice. It isn't accurate.
I will try to put together a sample and post. It really is something Microsoft needs to add, we were able to do this in PWA/MSPO and the hierarchy was built in automatically.
ThanksSClark513 Ok, depending upon how you want to display the data in Power BI you can use the fields below to configure your report.
The following is assuming you are calculating SPI at the task level using the msdyn_projecttasks table and the fields below are also in this table.
- msdyn_ismilestone - Identifies if task is a milestone. Use this to exclude milestone tasks as SPI most likely isn't relevant
- msdyn_summary - Identifies if task is summary or subtask
- msdyn_displaysequence - Indicates the display order of the task in Planner
- msdyn_outlinelevel - Identifies the outline level of the task
