Hello,
I am racking my over how to do this, so I am turning to the illustrious BI community.
I have to compute value by customer and by year yet still have the detail. Here's my example:
Customer A has three contracts signed over two years. Contracts 1 and 2 are valued at $1.2 million and were signed in 2016. Contract 3 is valued at $500,000 and was also signed in 2017. Contract 4 was signed in 2018 for $2 million. The company has taken out an insurance policy that covers each of these contracts. The policy has a $1 million cap for each policy year. The amount of covered is similar to tax withholding; namely, it is tiered.
Contract Amount Year Pct Coverage Limit
1 $400,000 2016 33% $333,000
2 $800,000 2016 67% $666,667
2016 Total $1,200,000 100% $1,000,000
3 $500,000 2017 100% $500,000
4 $1,000,000 2018 100% $1,000,000
Total $2,700,000 $2,500,000
I have the following derived column at the contract level:
Coverage Limit = var Amount = [Contract Amount]
var CarryOver = SUMX(FILTER('Insurance', [CompoundKey] -- policy type & year -- = 'Contract'[CompoundKey] && Amount >= 'Insurance'[PolicyMin] && Amount < 'Insurance'[PolicyMax]), [CarryOverAmount]
var MinimumAmt = SUMX('FILTER('Insurance'[CompoundKey] = 'Contract'[CompoundKey] && Amount >= [PolicyMin] && Amount < [PolicyMax]), [AmountMinimum])
var AppliedPercent = SUMX(FILTER('Insurance', 'Insurance'[CompoundKey] = 'Contract'[CompoundKey] && [Amount] >= [PolicyMin] && [Amount] < [PolicyMax]), [Applied Percent])
return CarryOver + ((Amount-MinimumAmt) * AppliedPercent)
The insurance table is a stand-alone table, no joins to any other table.
At the customer level, I have used the following formula for a derived column: Gross Limit = SUMX(CALCULATETABLE('Contract', FILTER('Contract', 'Contract'[CustomerID] = 'Customer'[CustomerID])), [ContractAmount])
At the contract level, I have used this formula for a derived column to arrive at a the pro-rata share: CALCULATE([ContractAmount]/RELATED('Customer'[Gross Limit]. This gives me my percentages, but here is the problem: it comes up with percentages based on the sum of all the amount across all years. I need to narrow the scope to each policy year.
As always, any guideance truly appreciated!
Tom
