Forum Discussion

SClark513's avatar
SClark513
Copper Contributor
Sep 06, 2024

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 which rows should be averaged to their main task. I am doing this in Power BI with calculated columns to figure the expected percent complete and compare to actual.

SPI = actual % complete DIVIDED BY expected % complete

(We are not including resources or costs - just looking at project tasks status and due dates)

 

Has anyone done this? I know it is going to be fairly complicated as Microsoft is not giving us any help here on their template. In general, it is really hard to even see the task hierarchy without some major manipulation. it's almost easier to export your project into excel and then bring this data into Power BI. Maybe I will try this!

 

At this point, if we can't figure it out - we may have to go back to Project Web App - which is not ideal at all. SPI shows if the tasks are on track or not. Our current workaround has flaws as we are not able to roll up sub task SPI to the main tasks. We have many levels - up to 6 at times - how do you roll up correctly with automation?

 

Thanks for reading - any guidance appreciated!

Project

3 Replies

    • SClark513's avatar
      SClark513
      Copper Contributor
      Sep 12, 2024
      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.

      Thanks
      • RodFromm's avatar
        RodFromm
        Iron Contributor
        Sep 12, 2024

        SClark513 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

Resources