Forum Discussion

tlenzmeier's avatar
tlenzmeier
Helper II
8 years ago

Summing Values Based on a Multiplied Value

I am trying to do a cumulative sum based on a value derived from a percentage multiplied by a fixed amount. I need to generate a burnoff of the fixed amount. The attached spreadsheet provides an example. The amount is $14.9 million, and the duration of the contract is 30 months. There are two tables involved. The first is an amortization type table with multiple durations and the corresponding rates. The other is a contract table that has the calculated contract amount.  The one measure that I have is MAX('Burnoff'[Burnoff]) *  SUM('Contract'[Amount]). In a simple table, the rates total 100% and the amount totals $14.9 million. The contract table is joined to my calendar table(ContractDate<->Date).  The burnoff table is joined to the calendar table on the respective date fields. I have tried using CALCULATE(SUMX( . . . . but that doesn't work. I've tried using CALCULATE([Value], FILTER(ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]))) and that doesn't work.

 

Any ideas??

 

Sample Burnoff

Need Help

2 Replies

  • tlenzmeier's avatar
    tlenzmeier
    Helper II
    8 years ago

    Sorry, left out another join: 'Project'[ProjectKey] = 'Burnoff'[ProjectKey].

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi tlenzmeier,

       

      There is only one table in the sample. Can you share a complete sample? Since SUM('Contract'[Amount]) is fixed, I would suggest you use a VAR like below.

      Measure = 
Var total = calculate( SUM('Contract'[Amount]), all(datetable))
      return

      Best Regards,

      Dale