Best performing way to do this?

sreachard

New Member
Joined
Aug 14, 2012
Messages
17
I've been fighting with calculate for awhile (and losing!). The pattern i'm trying to solve is something common to almost every measure I would write.
I need to sum Premium for insurance policies for a selected time period.
the policies have multiple endorsements that caould be in effect for the time period.
I need only the maximum endorsement for a period.
the measure (or measures) will need to roll up to the account and business.

example:
time period january and february 2010

policy endorse effdate trans amt Premium
513 0 2010-01-01 422491.00 422491
513 1 2010-01-01 801.00 423292
513 2 2010-01-01 0.00 423292
513 3 2010-02-27 -799.00 422493
513 4 2010-03-11 628.00 423121

so for the policy above endorsement 3 is the max for the jan feb 2010 timeperiod and the premium is 422493

I've tried qite a few ways to do this and some even worked but were very slow

i think i need to create a measure with calculate to get the max endorsement for the period
then use sumx with that measure filtering the policies, i did get a variation of that to almost work but I couldn't get a distinct list of policies, it calculated for each policy and endorsement. If anyone can give me some suggestions i'd really apprecieate it, I'm about ready to give up on this working for us.

here is a link to a blank pivot with sample data:

http://sdrv.ms/Q7Us3U


Thanks

Scott
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi Scott,

Have you tried a calculation like this one?

CALCULATE( VALUES(Table1[Premium]) , FILTER(Table1, Table1[endorse] = MAX(Table1[endorse]) ) )

if you place, for example, effdate as a slicer, then select jan and feb, and then play policy on pivot table rows, the expression above will give you the value of 422493 - the premium for the max policy in that period.

Javier Guillen
 
Upvote 0
Wow, that works much better, but i still have one issue with it.

I created a measure
PolicyPremium :
=CALCULATE(values(fact[annualpremium]) , FILTER(policy, policy[ENDORSEMENTNBR] = MAX(policy[ENDORSEMENTNBR]) ) )

created a second measure with sumx (i need that to get a row context right?)
SumPolicyPremium :
=sumx(DISTINCT(policy[Policy]),[PolicyPremium])

created a third measure to gilter the policies:
ExpiringPremium :
=calculate([sumPolicyPrem],filter(policy,policy[POLICYEXPIRATIONDATE]>=date(2012,07,01) &&policy[POLICYEXPIRATIONDATE]<=date(2012,07,31)))

that mostly works, but when i include policy in my rowlabels i get nothing?
I also tried to add a count of distinct policies, that killed performance.

Link to updaeted workbook
http://sdrv.ms/QogMnS


Thanks!!

 
Upvote 0
hi sreachard

The measure [ExpiringPremium] is actually computing a value at the policy level. The problem is you have your policy list filtered for 2 policies that fall out of the expiration date.

Try clicking on the drop down next to 'row lables' in the pivot table, then on the 'select a field' dropdown select 'policy' and then click on the red X for 'clear filter'. You will now see numbers at the policy level.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top