I am at a bit of a loss on how best to proceed. I need to do a complex calculation based on values from two different tables. I thought I create variables and then do my calculation from there, but I ran into an issue where I could use my SUMX function and join to one table, but not another.
Here's the scenario:
Customer A has three contracts for $500,000 each. They total $1.5 million. At a row-level context, there's a calculation we do based on contract number that is similar to income tax rates. A liability amount is calculated based on a progression. The first $100,000 is excluded, then the next $250,000 is covered at 80% and anything over $350,000 is excluded. The formula for that is done and working. The twist on all of this is that there is a policy limit of $1 million. We are not liable for anything in excess of that at a customer level. In this example the total liability is capped at $1 million. The customer level context is obviously a step higher than the contract level.
My data looks something like this:
Customer (table)
CustomerKey (PK), CustomerCode, CustomerName
Contract (table)
ContractKey (PK), CustomerKey (FK), ContractNumber, ContractAmount
Insurance (table)
PolicyKey (PK), InsuranceType, PolicyYear, MinimumAmount, MaximumAmount, Exposure, AppliedPct
I have a one-to-many join from customer to contract. The insurance table is not joined to anything and behaves more like a lookup. We have a compound key (insurance type + policy year) that serves as a join to the contract table.
Sample data looks like this:
Customer
Customer Key 501
CustomerCode 49113
Contract
ContractKey 34
ContractNumber 480, 481, 482,
CustomerKey 501
ContractAmount $500,000, $500,000, $500,000
Insurance
CompoundKey: DefIns 17-18
Type: DefIns
ApplicedPct: 1.0
CompoundKey: DefIns 17-18,
Type: DefIns,
PolicyYear 17-18,
AppliedPct 0.8
CompoundKey: DefIns 17-18,
Type: DefIns,
PolicyYear 17-18,
AppliedPct 0.8
I started writing out something like this: Amount = var DefIns = SUMX(FILTER('Insurance', 'Insurance'[CompoundKey] = 'Contract'[CompoundKey] . . .
Only half of the join works.
Finally, is this a calculation that PowerBI can do? Or should this be done in SQL?
Thanks.
