Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@tlenzmeier,
Could you please share dummy data of the three tables so that I can apply your DAX to test?
Regards,
Lydia
Here's a link to a dummy PBIX file. https://adolfsonpeterson-my.sharepoint.com/:u:/g/personal/tlenzmeier_a-p_com/EfCOycXGn7FOnx9HcC3TBbg...
I didn't include the percentages since that's part of my problem.
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |