LOOKUPVALUE() to CALCULATE() + SUM()?

[bmsteve]

Hi,

This is my first post, so I apologize for any mistakes I make.

I am using the following LOOKUPVALUE() function is in a measure:

= LOOKUPVALUE( tblExposures[EE], tblExposures[CY], tblClaims[AY] )

This function returns the correct value as long as there are no tblExposures[CY] repeats.
If there are tblExposures[CY] repeats, I get the following error:

ERROR - CALCULATION ABORTED: Calculation error in measure 'tblClaims'[Adjusted Paid Frequency]: A table of multiple values was supplied where a single value was expected.

I want to sum these multiple values. I feel like this is an easy problem to fix and I think I can do this using CALCULATE() and SUM(), but I can't figure it out.

Any help would be greatly appreciated.

 

[bmsteve]

I'm using Excel 2010.

 

[miguel.escobar]

you might want to use CALCULATE and then FILTER(the table, your LOOKUPVALUE formula = VALUES(your column))
Hopefully that works but there might be an easier formula. Hard to tell since I'm just imagining your data model (tables) right now.

Best!

 

[bmsteve]

you might want to use CALCULATE and then FILTER(the table, your LOOKUPVALUE formula = VALUES(your column))
Hopefully that works but there might be an easier formula. Hard to tell since I'm just imagining your data model (tables) right now.

Best!

Thanks for replying miguel. Below is the data model I'm working with.

AY = Accident Year (the year accidents occurred in)
CY = Calendar Year (the year payments for accidents were made)
Paid Claim = The number of claims paid during a given Calendar Year for accidents that occurred in a given Accident Year
EE = Earned Exposures (the number of exposures for which coverage was provided during a given Calendar Year)

For example, the first row of the tblClaims table means that 4000 claims that occurred in Accident Year 0 had payments made in Calendar Year 2.
So there was a 2 year lag for these claims between occurrence and payment.

My goal is to match Accident Year Paid Claims to Calendar Year Earned Exposures.
For every one Calendar Year, there are payments made from claims that occurred in many different Accident Years.
The measure that I am using the LOOKUPVALUE() function in is called "Adjusted Paid Frequency" or APF and it looks like this:

=SUMX( tblClaims, tblClaims[Paid Claim] / LOOKUPVALUE( tblExposures[EE], tblExposures[CY], tblClaims[AY] ) )

This returns the sum of incremental frequencies ( = Paid Claims / Earned Exposures ) for a given Calendar Year.
For example,

APF for CY 2 = ( Paid Claims for AY 0, CY 2 / EE for CY 0 ) + (Paid Claims for AY 1, CY 2 / EE for CY 1 ) + ( Paid Claims for AY 2, CY 2 / EE for CY 2 )

The LOOKUPVALUE() formula works great for this data model since the Earned Exposures are summarized by Calendar Year (e.g. there is only 1 possible value for EE for CY 0). However, I need to expand this formula for the data model that has the Earned Exposures at a finer level (e.g. the data is at the policy level and I need to sum the Earned Exposures across all policies for a given Calendar Year).


tblClaims:

AY CY Paid Claims
0 2 4000
1 2 6000
1 3 4000
2 2 10000
2 3 6000
2 4 4000
3 3 10420
3 4 6252
3 5 4168
4 4 11127.50
4 5 6676.50
4 6 4451
5 5 12270
5 6 7362
6 6 13950

tblExposures:

CY EE
0 100000
1 100000
2 100000
3 104200
4 111275
5 122700
6 139500

The Adjusted Paid Frequency for all Calendar Years should be 0.2.
If you drop the Adjusted Paid Frequency measure into a PivotTable with AY Row Labels and CY Column Labels, you should see "blocks" of 0.04, 0.06, and 0.10.
The 0.04 should be the top diagonal, 0.06 should be the middle diagonal, and 0.10 should be the bottom diagonal.

I hopes this helps explain my problem a little better. The VALUES() function returns a one-column table of distinct values and I need the sum of all of the relevant Earned Exposures. For example, if I were to add a row to the tblExposures table (CY = 6, EE = 0), I would need the formula to sum the 139500 and the 0 instead of error out.

All the best,

Brandon

 

[miguel.escobar]

Hey Brandon,

Do you think that you could upload the workbook (to Skydrive, Gdrive, Dropbox or any other) so I could download it and take a look at it? It's hard for me to visualize the data even with your previous message.

There should be a relationship between CalendarYear and AccidentYear in the best case scenario so you can just use the relationship instead of LOOKUPVALUE. VALUES() should work but it depends on how your pivot table looks like and what's on your slicers - still something that I need to visualize on your workbook.

 

[bmsteve]

Sure, here's the link.

styrsky2.xlsx

 

[miguel.escobar]

Brandon,

I'm really sorry. I'm completely lost - it's probably just me that can't figure out what's going on in that file but, just a recommendation, you could create a relationship with the CY or another table instead of using the LOOKUPVALUE function. This should help you A LOT and I still can't figure out if you need a row-by-row iteration or something else, for example, that you need to calculate for every row something based on that LOOKUP or you need to LOOKUP based on the aggregation made for all the rows that meet the criteria of a YEAR.

Best of luck!

 

[bmsteve]

Miguel,

I tried to clean up the workbook and add documentation here:

styrsky2.1.xlsx

However, I think the workbook makes the problem I'm trying to fix more difficult than it needs to be.
The only thing that I need to change is the Adjusted Paid Frequency Denominator Measure.
And this is just a way to group the Earned Exposures from the tblExposures table!

Adjusted Paid Frequency Denominator:=SUMX( tblClaims, LOOKUPVALUE( tblExposures[EE], tblExposures[CY], tblClaims[AY] ) )

In this model, the LOOKUPVALUE() function returns the correct value because there is only one record with a tblExposures[CY] that matches the tblClaims[AY].
(e.g., there is only 1 CY 0 in the tblExposures table, so the Earned Exposures can be "looked up")
In the ultimate model, there are many records with a tblExposures[CY] that matches the tblClaims[AY].
(e.g., there are 5 CY 0s in the tblExposures table, the Earned Exposures need to be summed)

I think the ultimate Adjusted Paid Frequency Denominator formula should look something like this:

Adjusted Paid Frequency Denominator:=SUMX( tblClaims, CALCULATE( SUM( tblExposures[EE] ), FILTER( tblExposures[CY] = tblClaims[AY] ) )

but I just can't figure out how to pass the CY, AY match into the filter like it is in the LOOKUPVALUE() formula.
If this doesn't make sense, it's ok. I've been struggling with this for quite some time now and I might just need to let it go.

All the best,

Brandon

 

[Nunz2]

Miguel,

I tried to clean up the workbook and add documentation here:

styrsky2.1.xlsx

However, I think the workbook makes the problem I'm trying to fix more difficult than it needs to be.
The only thing that I need to change is the Adjusted Paid Frequency Denominator Measure.
And this is just a way to group the Earned Exposures from the tblExposures table!

Adjusted Paid Frequency Denominator:=SUMX( tblClaims, LOOKUPVALUE( tblExposures[EE], tblExposures[CY], tblClaims[AY] ) )

In this model, the LOOKUPVALUE() function returns the correct value because there is only one record with a tblExposures[CY] that matches the tblClaims[AY].
(e.g., there is only 1 CY 0 in the tblExposures table, so the Earned Exposures can be "looked up")
In the ultimate model, there are many records with a tblExposures[CY] that matches the tblClaims[AY].
(e.g., there are 5 CY 0s in the tblExposures table, the Earned Exposures need to be summed)

I think the ultimate Adjusted Paid Frequency Denominator formula should look something like this:

Adjusted Paid Frequency Denominator:=SUMX( tblClaims, CALCULATE( SUM( tblExposures[EE] ), FILTER( tblExposures[CY] = tblClaims[AY] ) )

but I just can't figure out how to pass the CY, AY match into the filter like it is in the LOOKUPVALUE() formula.
If this doesn't make sense, it's ok. I've been struggling with this for quite some time now and I might just need to let it go.

All the best,

Brandon

Hey Brandon,

Funny I ha the same exact issue wit Calendar Year Accident Year. The way I solved it was to create an active join on the claims and an inactive join on the premium. Then when you use calculate for the calendar year premium , use userrelationship with your inactive join as the arguments. Hope this helps.