cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Summarizing Data By Year and Detail

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

2 REPLIES 2
Highlighted
Microsoft

## Re: Summarizing Data By Year and Detail

@tlenzmeier,

Could you please share dummy data of the three tables so that I can apply your DAX to test?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II

## Re: Summarizing Data By Year and Detail

I didn't include the percentages since that's part of my problem.

Thanks!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors