I am running the Earned Value Over Time visual report from MS Project 2010 and would like to accumulate baseline cost for multiple baselines in Excel. Since the Excel pivot table is linked to the .cub file that Project exports it won't allow me to modify the table data. My plan was to add a column and use DAX or formula to accumulate the baseline cost fields but it won't allow any modification. Does anyone know how to get it to allow modification? Or a way to accumulate costs for multiple baselines? The end goal is to see our original cumulative baseline cost against the current or other baselines we have taken.
1 Answer
I found that this can be accomplished using MDX(Multidimensional eXpressions) via the Excel PivotChartTools - Analyze tab. Under the Calculations section of the ribbon there is an OLAP Tools drop arrow. You can create calculated measures using MDX to accumulate your Baseline Cost fields and add them as measure to the pivot table and chart. The expression I used for Baseline1 Cost was "SUM({NULL:[time]},[baseline1 cost])". I created a measure for each baseline 0 through 10.
It appears that OLAP cubes are not editable via the PowerPivot tools as I was first trying.