Forum Discussion

rawiswarden's avatar
rawiswarden
Icon for Helper I rankHelper 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...
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

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity 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
    Icon for Helper I rankHelper 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
      Icon for Helper I rankHelper 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.