PowerPivot - Allocation of values to Bands

Patro

New Member
Joined
May 8, 2019
Messages
6
Hi,

I want to allocate some limit values into Limit Bands based on certain conditions. I have two tables in PowerPivot and I am trying using DAX functions to do it. The two tables are the following,

1. A table with Attachment Limit values (Named "Exposure") with the following fields,


ID
Attachment
Limit
1
20,000,000
50,000,000
2
30,000,000
80,000,000

<tbody>
</tbody>

2. A table (Named "Bands") with the limit bands,
Limit Bands
Min Limit
Max Limit
0M-25M
0M
25M
25M-50M
25M
50M
50M-75M
50M
75M
75M-100M
75M
100M
100M-150M
100M
150M
150M-250M
150M
250M
250M-500M
250M
500M
500M-1000M
500M
1,000M
1000M-1000M+
1,000M
1,000,000M

<tbody>
</tbody>


What I want to do is to allocate the Limits of the first table (Named "Exposure") to the Limit Bands of the second table. The "Attachment" is the amount after which the Limit starts. For example for ID=1 the limit starts at 20M hence the allocation of the Limit to the band will start in the first band and will be as follows,
Limit Bands
Min Limit
Max Limit
Allocation
0M-25M
0M
25M
5M
25M-50M
25M
50M
25M
50M-75M
50M
75M
20M
75M-100M
75M
100M
0M
100M-150M
100M
150M
0M
150M-250M
150M
250M
0M
250M-500M
250M
500M
0M
500M-1000M
500M
1,000M
0M
1000M-1000M+
1,000M
1,000,000M
0M

<tbody>
</tbody>

For ID=2 the allocation should start from the second Band as the attachment point is 30M and should be as follows,



Limit Bands
Min Limit
Max Limit
Allocation
0M-25M
0M
25M
0M
25M-50M
25M
50M
20M
50M-75M
50M
75M
25M
75M-100M
75M
100M
25M
100M-150M
100M
150M
10M
150M-250M
150M
250M
0M
250M-500M
250M
500M
0M
500M-1000M
500M
1,000M
0M
1000M-1000M+
1,000M
1,000,000M
0M

<tbody>
</tbody>


For both IDs, the allocation should be,
Limit Bands
Min Limit
Max Limit
Allocation
0M-25M
0M
25M
5M
25M-50M
25M
50M
45M
50M-75M
50M
75M
45M
75M-100M
75M
100M
25M
100M-150M
100M
150M
10M
150M-250M
150M
250M
0M
250M-500M
250M
500M
0M
500M-1000M
500M
1,000M
0M
1000M-1000M+
1,000M
1,000,000M
0M

<tbody>
</tbody>

I worked out a formula that works for a single row table (that is when I only have one limit to allocate). When I add more rows to the table, I think there is an issue with the aggregation of the amounts. Could you please help?

The formula that works for a single row in Exposure table is the following,

SUMX( 'Bands',
IF(VALUES(XoL_Exposure[Sum of Att+Limit])<FIRSTNONBLANK(Bands[Max Limit],1),

MAX(VALUES(XoL_Exposure[Sum of Att+Limit])-FIRSTNONBLANK(Bands[Min Limit],1),0),

IF(VALUES(XoL_Exposure[Attachment])<FIRSTNONBLANK(Bands[Max Limit],1),

MIN(MAX(FIRSTNONBLANK(Bands[Max Limit],1)-VALUES(XoL_Exposure[Attachment]),0), FIRSTNONBLANK(Bands[Max Limit],1)-FIRSTNONBLANK(Bands[Min Limit],1))
)
)
)

Any help will be much appreciated!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sorry, I just noticed that the conditions in my formula are not shown. See below the formula that works for a single row table,


SUMX( 'Bands',
IF(VALUES(XoL_Exposure[Sum of Att+Limit]) < FIRSTNONBLANK(Bands[Max Limit],1),

MAX(VALUES(XoL_Exposure[Sum of Att+Limit])-FIRSTNONBLANK(Bands[Min Limit],1),0),

IF(VALUES(XoL_Exposure[Attachment]) < FIRSTNONBLANK(Bands[Max Limit],1),

MIN(MAX(FIRSTNONBLANK(Bands[Max Limit],1)-VALUES(XoL_Exposure[Attachment]),0), FIRSTNONBLANK(Bands[Max Limit],1)-FIRSTNONBLANK(Bands[Min Limit],1))
)
)
)

Any help will be much appreciated!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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