cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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
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
Helper II

Re: Summarizing Data By Year and Detail

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!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

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

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