Pivot Table totals with %'s (prevent it from SUMing and replace with calculation)

[Vexorg]

Hi all,

This is the the issue im having. Below the LTV is calculated by dividing the Loan Amount by the Appraised amount. When I create a pivot table it simply sums the LTV values, as opposed to calcluating them correctly.

I.E. below it would sum the LTV value as 147.75% instead of using the proper division and coming up with 76.92%

How can I correct this?
[TABLE="width: 500"]
<tbody>[TR]
[TD]Appraised[/TD]
[TD]Loan Amount[/TD]
[TD]LTV[/TD]
[/TR]
[TR]
[TD]75[/TD]
[TD]50[/TD]
[TD]66.67%[/TD]
[/TR]
[TR]
[TD]185[/TD]
[TD]150[/TD]
[TD]81.08%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry Sullivan]

Hi Vexorg,

Are you using the PowerPivot Add-in? You've posted this question in the PowerPivot forum, but I suspect you are just working with a standard Excel PivotTable.

If that's the case, then you'll need to add a Calculated Field in your PivotTable
='Loan Amount'/Appraised
...instead of trying to use the LTV field in your source data.

 

[Vexorg]

Hi Vexorg,

Are you using the PowerPivot Add-in? You've posted this question in the PowerPivot forum, but I suspect you are just working with a standard Excel PivotTable.

If that's the case, then you'll need to add a Calculated Field in your PivotTable
='Loan Amount'/Appraised
...instead of trying to use the LTV field in your source data.

JS411, thank you for your response. Yes I am using the powerpivot add-in.

 

[Vexorg]

JS411, thank you for your response. Yes I am using the powerpivot add-in.

Any ideas?

 

[Jerry Sullivan]

For PowerPivot, the same principle applies however you could create a Measure instead of a Calculated Field.

Here are the steps.....

Select a cell on PivotTable
On the Ribbon > PowerPivot tab > Measures group > New Measure
Complete fields:
Table Name: Table1 (select your actual table name from list, which might be different than Table1)
Measure name: LTV_Measure
Custom name: LTV_Measure
Description: Loan to Value Ratio (this is optional field)
Formula:
=Table1[Sum of Loan Amount]/Table1[Sum of Appraised]

(When you begin typing the formula, pick from the Intellisense dropdowns that appear to ensure you match the field names exactly).
Check formula to ensure no errors.
Apply formatting to field (optional)
Then OK.

 

[Vexorg]

For PowerPivot, the same principle applies however you could create a Measure instead of a Calculated Field.

Here are the steps.....

Select a cell on PivotTable
On the Ribbon > PowerPivot tab > Measures group > New Measure
Complete fields:
Table Name: Table1 (select your actual table name from list, which might be different than Table1)
Measure name: LTV_Measure
Custom name: LTV_Measure
Description: Loan to Value Ratio (this is optional field)
Formula:
=Table1[Sum of Loan Amount]/Table1[Sum of Appraised]

(When you begin typing the formula, pick from the Intellisense dropdowns that appear to ensure you match the field names exactly).
Check formula to ensure no errors.
Apply formatting to field (optional)
Then OK.

Hmm I tried this but I received the following error:

The value for column 'LoanAmtMM' in table 'Financing Pipeline' 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.

I have no blank fields, or circular references. Ecah field has only 1 value.

 

[Jerry Sullivan]

In my example, I had already placed the fields Loan Amount and Appraised in the Values area of the PivotTable and that created additional fields called
[Sum of Loan Amount] and [Sum of Appraised]

I then used those aggregated fields in this Measure formula...

=Table1[Sum of Loan Amount]/Table1[Sum of Appraised]

In lieu of creating those summary fields, you can use this formula syntax for the LTV_Measure field (modify to match your base field names)..

=Sum(Table1[Loan Amount])/SUM(Table1[Appraised])

 

[Vexorg]

In my example, I had already placed the fields Loan Amount and Appraised in the Values area of the PivotTable and that created additional fields called
[Sum of Loan Amount] and [Sum of Appraised]

I then used those aggregated fields in this Measure formula...

=Table1[Sum of Loan Amount]/Table1[Sum of Appraised]

In lieu of creating those summary fields, you can use this formula syntax for the LTV_Measure field (modify to match your base field names)..

=Sum(Table1[Loan Amount])/SUM(Table1[Appraised])

Thanks, this worked perfectly!