Forum Discussion

rawiswarden's avatar
rawiswarden
Helper I
6 years ago
Solved

Cumulative Loss Curves

Hello,

 

I am trying to do a calculation in Power BI but I am unsure how to structure it.  

 

I need to calculate conditional cumulative losses and balances over time.  The X axis is months since origination, which is Duration in the below  table.  I have to divide the cumulative loss (Loss column) by the starting balance (Balance).

 

 

I then sum based on Duration and Age to determine the balances that have been outstanding for a given period of time and their associated losses.  Ultimately I am producing a hazard curve.

 

 

 

 

My goal is to allow the user to drop in any dimension and produce the relevant curves for that classifier. 

 

I can't figure out how to generate an index value of time (Months outstanding) and then do SUMX based on those calculations.  I am trying to learn DAX and not create the script in Python.

 

Any help is appreciated!

 

The Excel fromulas used are below.  I couldn't figure out how to attach the sheet.

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi rawiswarden 

    You could use this formula to create a new table as below:

    New Table = 
ADDCOLUMNS (
    SELECTCOLUMNS ( 'Table', "Time Period", 3 * 'Table'[ID] ),
    "Balance", CALCULATE (
        SUM ( 'Table'[Balance] ),
        FILTER ( 'Table', 'Table'[Age] >= [Time Period] )
    ),
    "Cumulative Loss", CALCULATE (
        SUM ( 'Table'[Loss] ),
        FILTER (
            'Table',
            'Table'[Age] >= [Time Period]
                && 'Table'[Duration] <= [Time Period]
        )
    )
)

    and then add a calculated column as below:

    Percent = 'New Table'[Cumulative Loss]/'New Table'[Balance]

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi rawiswarden 

    You could use this formula to create a new table as below:

    New Table = 
ADDCOLUMNS (
    SELECTCOLUMNS ( 'Table', "Time Period", 3 * 'Table'[ID] ),
    "Balance", CALCULATE (
        SUM ( 'Table'[Balance] ),
        FILTER ( 'Table', 'Table'[Age] >= [Time Period] )
    ),
    "Cumulative Loss", CALCULATE (
        SUM ( 'Table'[Loss] ),
        FILTER (
            'Table',
            'Table'[Age] >= [Time Period]
                && 'Table'[Duration] <= [Time Period]
        )
    )
)

    and then add a calculated column as below:

    Percent = 'New Table'[Cumulative Loss]/'New Table'[Balance]

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

    • rawiswarden's avatar
      rawiswarden
      Helper I
      6 years ago

      Hello v-lili6-msft !

       

      Thanks for the example.  You solved a couple of my issues in terms of structuring the table.  I didn't know I could multiply the ID like that, for example.

       

      Now, say I want to add category to the legend of a line graph which shows the percentage calculation for each.  Is this possible?  I'm not sure how to make these calculations conditional in this manner.   Do I just need to create seperate visuals and filter the data on each visual?

       

      • rawiswarden's avatar
        rawiswarden
        Helper I
        6 years ago

        Hello,

         

        I believe I figured this out.  I was trying to avoid manually creating a table, so if anyone can explain how to do this automatically I would be very happy.

         

        I used "Enter Data" to create a new table and manually entered each time period (1 to N).  I think I stopped at 50.  This was the step I was trying to do in DAX.  I tried with GENERATESERIES  but it does not appear to work in a way that would work.

         

        I created measures using the sort of calculations above and then linked the fact table used in the calculations to the dimension tables.  The table where the calculations occur is not linked to anything.