Forum Discussion

YavuzDuran's avatar
YavuzDuran
Helper III
5 years ago

Cumulative Calculation in a Matrix Table

Hi all

 

I have a matric table as follow

 

 

 

and I need to put cumulative % per Month (ie. Jan 3 , it is gonna be 0 + 1.2 + 2.4 = 3.6)

 

The Percentage calculation above is:

Charge Off % - Loan Life = divide(SUM('Loan Performance'[Charge Off ?]),
calculate(countrows('Loan Performance'),allselected('Loan Performance'[Loan Life (Month Period)])),0)
 
where Loan Life (Month Period) is the column values in above table
 
For Cumulative calculation, I put such a formula : 
Charge Off % - Loan Life - cumulative =
calculate(divide(SUM('Loan Performance'[Charge Off ?]),
calculate(countrows('Loan Performance'),allselected('Loan Performance'[Loan Life (Month Period)])),0),
filter(allselected('Loan Performance'),
'Loan Performance'[Loan Life (Month Period)]<=max('Loan Performance'[Loan Life (Month Period)])
&& 'Loan Performance'[Contract Month]=SELECTEDVALUE('Loan Performance'[Contract Month])))
 
it calculated like this 
 

 

 
but it is wrong
 
Your help is appreciated
Thank you 
 

 

Need Help

8 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    YavuzDuran 

    Can you try the following formula and update me, I did not understand the requirement and the data model fully to be honest.

    Charge Off % - Loan Life = 
var __currentperiod = SELECTEDVALUE('Loan Performance'[Loan Life (Month Period)])
var __charge = 
    CALCULATE(
        SUM('Loan Performance'[Charge Off ?]),
        'Loan Performance'[Loan Life (Month Period)] <= __currentperiod, 
        ALLSELECTED('Loan Performance'[Loan Life (Month Period)])
    )  
var __rows = 
     CALCULATE(
        COUNTROWS('Loan Performance'),
        'Loan Performance'[Loan Life (Month Period)] <= __currentperiod, 
        ALLSELECTED('Loan Performance'[Loan Life (Month Period)])
    )  
var result = 
    DIVIDE( __charge , __rows , 0 )

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, YavuzDuran 

    Please try the below.

    Instead of using allselected('Loan Performance'[Loan Life (Month Period)]), replace it to ALLEXCEPT ('Loan Performance', 'Loan Performance'[Contract Month])

     

     

    Charge Off % - Loan Life - cumulative =
    CALCULATE (
    DIVIDE (
    SUM ( 'Loan Performance'[Charge Off ?] ),
    CALCULATE (
    COUNTROWS ( 'Loan Performance' ),
    ALLEXCEPT ( 'Loan Performance', 'Loan Performance'[Contract Month] )
    ),
    0
    ),
    FILTER (
    ALLSELECTED ( 'Loan Performance' ),
    'Loan Performance'[Loan Life (Month Period)]
    <= MAX ( 'Loan Performance'[Loan Life (Month Period)] )
    && 'Loan Performance'[Contract Month]
    = SELECTEDVALUE ( 'Loan Performance'[Contract Month] )
    )
    )

     

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • YavuzDuran's avatar
      YavuzDuran
      Helper III
      5 years ago

      ..

       

      The sold Numbers in between months 1-15 = 63

      So the cumulative % should be  63/160 = %39.37

       

      But it shows %3.4 on the 15 the month in below table 

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi, YavuzDuran 
        Thank you for your feedback.

        Please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

        Thanks.