powerpivot 2010 pivot table new measure for percentage difference

0
votes

I'm a newbie with PowerPivot 2010. I've created a few reports, but I'm having difficulty with a report that I want to create that requires a new measure. In my volume table I have 2 columns in my volume table (tblPilot_Volume) - Current Wk Vol This and Current Wk Vol Last. I want to display in the pivot the percentage difference between these two values.

I tried the calculation below but it doesn't work. I'm not sure what I'm doing wrong. 

=sum(tblPilot_Volume[Current Wk Vol This]-(tblPilot_Volume[Current Wk Vol Last])/(tblPilot_Volume[Current Wk Vol Last])

Prior to this I tried to just add a column to the PowerPivot table with the formula. The calculations works in the table, but when I create the pivot table it doesn't work.

I also need to eliminate any rows where there is a zero in the Current wk Vol Last column.

1
percentagepowerpivotvariance

1 Answers

0
votes

ME Miller, I would try this:

=(sum(tblPilot_Volume[Current Wk Vol This])-sum(tblPilot_Volume[Current Wk Vol Last])) / sum(tblPilot_Volume[Current Wk Vol Last])

When creating measures, make sure you always use one of the aggregation functions (like sum, avg, min, max etc.).

Hope this helps.