Powerpivot - Allocation of values to Bands

[Patro]

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 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>

<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 in 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 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 hope my explanation is clear.

Your help is much appreciated!!!

 

[Patro]

Hi All,

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 limit value is the following,

Alloc:=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))

)
)
)

Where Att + Limit column is a calculated column that sums the limit and the attachment.

 

[Patro]

Sorry, the formula I am using 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))

)
)
)

 

[Patro]

SUMX('Bands',
IF(VALUES(XoL_Exposure[Sum of Att+Limit]) <firstnonblank(bands[max limit],1),
< 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),
< 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))

)
)
)</firstnonblank(bands[max></firstnonblank(bands[max>