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,


[TABLE="width: 332"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Attachment[/TD]
[TD]Limit[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20,000,000[/TD]
[TD]50,000,000[/TD]
[/TR]
[TR]
[TD] 2[/TD]
[TD] 30,000,000[/TD]
[TD] 80,000,000[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 332"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
2. A table (Named "Bands") with the limit bands,



[TABLE="width: 492"]
<tbody>[TR]
[TD]Limit Bands[/TD]
[TD]Min Limit[/TD]
[TD]Max Limit[/TD]
[/TR]
[TR]
[TD]0M-25M[/TD]
[TD]0M[/TD]
[TD]25M[/TD]
[/TR]
[TR]
[TD]25M-50M[/TD]
[TD]25M[/TD]
[TD]50M[/TD]
[/TR]
[TR]
[TD]50M-75M[/TD]
[TD]50M[/TD]
[TD]75M[/TD]
[/TR]
[TR]
[TD]75M-100M[/TD]
[TD]75M[/TD]
[TD]100M[/TD]
[/TR]
[TR]
[TD]100M-150M[/TD]
[TD]100M[/TD]
[TD]150M[/TD]
[/TR]
[TR]
[TD]150M-250M[/TD]
[TD]150M[/TD]
[TD]250M[/TD]
[/TR]
[TR]
[TD]250M-500M[/TD]
[TD]250M[/TD]
[TD]500M[/TD]
[/TR]
[TR]
[TD]500M-1000M[/TD]
[TD]500M[/TD]
[TD]1,000M[/TD]
[/TR]
[TR]
[TD]1000M-1000M+[/TD]
[TD]1,000M[/TD]
[TD]1,000,000M[/TD]
[/TR]
</tbody>[/TABLE]


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,

[TABLE="width: 581"]
<tbody>[TR]
[TD]Limit Bands[/TD]
[TD]Min Limit[/TD]
[TD]Max Limit[/TD]
[TD]Allocation[/TD]
[/TR]
[TR]
[TD]0M-25M[/TD]
[TD]0M[/TD]
[TD]25M[/TD]
[TD]5M[/TD]
[/TR]
[TR]
[TD]25M-50M[/TD]
[TD]25M[/TD]
[TD]50M[/TD]
[TD]25M[/TD]
[/TR]
[TR]
[TD]50M-75M[/TD]
[TD]50M[/TD]
[TD]75M[/TD]
[TD]20M[/TD]
[/TR]
[TR]
[TD]75M-100M[/TD]
[TD]75M[/TD]
[TD]100M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]100M-150M[/TD]
[TD]100M[/TD]
[TD]150M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]150M-250M[/TD]
[TD]150M[/TD]
[TD]250M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]250M-500M[/TD]
[TD]250M[/TD]
[TD]500M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]500M-1000M[/TD]
[TD]500M[/TD]
[TD]1,000M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]1000M-1000M+[/TD]
[TD]1,000M[/TD]
[TD]1,000,000M[/TD]
[TD]0M[/TD]
[/TR]
</tbody>[/TABLE]

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



[TABLE="width: 581"]
<tbody>[TR]
[TD]Limit Bands[/TD]
[TD]Min Limit[/TD]
[TD]Max Limit[/TD]
[TD]Allocation[/TD]
[/TR]
[TR]
[TD]0M-25M[/TD]
[TD]0M[/TD]
[TD]25M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]25M-50M[/TD]
[TD]25M[/TD]
[TD]50M[/TD]
[TD]20M[/TD]
[/TR]
[TR]
[TD]50M-75M[/TD]
[TD]50M[/TD]
[TD]75M[/TD]
[TD]25M[/TD]
[/TR]
[TR]
[TD]75M-100M[/TD]
[TD]75M[/TD]
[TD]100M[/TD]
[TD]25M[/TD]
[/TR]
[TR]
[TD]100M-150M[/TD]
[TD]100M[/TD]
[TD]150M[/TD]
[TD]10M[/TD]
[/TR]
[TR]
[TD]150M-250M[/TD]
[TD]150M[/TD]
[TD]250M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]250M-500M[/TD]
[TD]250M[/TD]
[TD]500M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]500M-1000M[/TD]
[TD]500M[/TD]
[TD]1,000M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]1000M-1000M+[/TD]
[TD]1,000M[/TD]
[TD]1,000,000M[/TD]
[TD]0M[/TD]
[/TR]
</tbody>[/TABLE]


For both IDs the allocation should be,

[TABLE="width: 581"]
<tbody>[TR]
[TD]Limit Bands[/TD]
[TD]Min Limit[/TD]
[TD]Max Limit[/TD]
[TD]Allocation[/TD]
[/TR]
[TR]
[TD]0M-25M[/TD]
[TD]0M[/TD]
[TD]25M[/TD]
[TD]5M[/TD]
[/TR]
[TR]
[TD]25M-50M[/TD]
[TD]25M[/TD]
[TD]50M[/TD]
[TD]45M[/TD]
[/TR]
[TR]
[TD]50M-75M[/TD]
[TD]50M[/TD]
[TD]75M[/TD]
[TD]45M[/TD]
[/TR]
[TR]
[TD]75M-100M[/TD]
[TD]75M[/TD]
[TD]100M[/TD]
[TD]25M[/TD]
[/TR]
[TR]
[TD]100M-150M[/TD]
[TD]100M[/TD]
[TD]150M[/TD]
[TD]10M[/TD]
[/TR]
[TR]
[TD]150M-250M[/TD]
[TD]150M[/TD]
[TD]250M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]250M-500M[/TD]
[TD]250M[/TD]
[TD]500M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]500M-1000M[/TD]
[TD]500M[/TD]
[TD]1,000M[/TD]
[TD]0M[/TD]
[/TR]
[TR]
[TD]1000M-1000M+[/TD]
[TD]1,000M[/TD]
[TD]1,000,000M[/TD]
[TD]0M[/TD]
[/TR]
</tbody>[/TABLE]

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>