Pivot Table Calculate Percentage Difference Between Two Columns

[lencepeda1]

MS Excel 2013

Hello, thanks for reading my thread. I have found similar posts regarding the same issue, however I have been unable to get it to work.

Basically, I have a Data Source from an SQL View that appears as a single Data Source Table in MS Excel Pivot Table. I am attempting to create a calculated field that will calculate the % difference between two columns in the data source. Currently both columns are represented in the Pivot table.

Here is an example of the data source - please note some currency fields contain blank values:

ClientID, Insured, ExpiringPremium, GrossPremium
111111, ABC Co, $1,000, $1,250
222222, ABC Co, $1,000, $1,000
333333, XYZ Co, $5,000



Here is an example of the Pivot Table, as you can see Client ID's are grouped within the Insured Column

Insured, ExpiringPremium, GrossPremium, %Change
ABC +
111111, $1,000, $1,250
222222, $1,000, $1,000

XYZ Co +
333333, $5,000

I would like to display the percentage change in the %Change column, but my calculated field formula doesn't work:

=(Cube_SocSvc_NB_ALL[GrossPremium]-Cube_SocSvc_NB_ALL[ExpiringPremium])/Cube_SocSvc_NB_ALL[ExpiringPremium]


Here is the error:

Calculation error in measure 'Cube_SocSvc_NB_ALL'[RenewalPremiumChange]: The value for column 'GrossPremium' in table 'Cube_SocSvc_NB_ALL' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.


Any advice would be greatly appreciated. Thanks in advance

 

[lencepeda1]

I ended up getting it to work by using a calculated field - here is the formula.

=divide(sum(Cube_SocSvc_NB_ALL[GrossPremium])-(sum(Cube_SocSvc_NB_ALL[ExpiringPremium])), (sum(Cube_SocSvc_NB_ALL[ExpiringPremium])))