3 Replies Latest reply on Feb 14, 2019 11:55 AM by Jim Dehner

# Pivot Table advanced-calculation column

Hi,

If I knew terminology better, I could probably find this topic somewhere here... .

In my original dataset, I have several columns: I have one column called "LOB", one called "DataType", one called "Year", and one called "Amount" .

LOBDataTypeYearAmount
LifePremium201525
LifeClaims20153
LifeClaims201619
DentalPremium201616
DisabilityClaims20184
DentalReserves201920
DisabilityReserves201813
DisabilityClaims20181
etc....

I have pivoted my data so that LOB is the row data, and Year and DataType are the column data.  The amount is summed for each.

LOB Claims Capitation Reserves Premium Calculation 2015 2015 2015 2015 2015 Life 12 0 2 20 =SUM(Claims, Capitation, Reserves)/Premium AD&D 3 0 1 6 =SUM(Claims, Capitation, Reserves)/Premium Dental 8 0 10 =SUM(Claims, Capitation, Reserves)/Premium Disability 7 0 3 13 =SUM(Claims, Capitation, Reserves)/Premium

I need to do the following calculation:

within each "Year", I need to SUM('Claims', 'Capitation', and 'Reserves') type data / 'Premium' type data and have it display to the right.

I'm new to Tableau, so please baby-talk me through it.

• ###### 1. Re: Pivot Table advanced-calculation column

Hi Joseph

please see the training videos on connecting to data and Prep Tableau Training: View Training Courses

you want the data in a tall narrow column lik

LOD     Date     Data Type     Value

that structure will make aggregation across multiple date periods much easier

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Pivot Table advanced-calculation column

Sorry - what I'm looking for is within a pivot table I've already created, I need to do an equation: (Claims+Capitation+Reserves)/Premium.

In the original dataset I have an amount column that is accompanied with a support column noting if the amount is Premium, Claims, Reserves or Capitation.  I have pivoted this so now I have a pivot header of Claims, Capitation, Reserves and Premium.  I need to add an additional column to the pivot table that is the above calculation.

• ###### 3. Re: Pivot Table advanced-calculation column

Yes I understand that - The data structure is not taking advantage of the database capabilities of tableau-

so you will need a separate calculation that takes the form of (sum(claims)+sum(claims)+sum(reserve))/ sum(premium)

and each time you want a different combination it will take another field

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.