Forum Discussion

Ncf5031's avatar
Ncf5031
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Subtract Column Value From Total Row Count - KM Survival Analysis

Needing help with two seemingly simple issues.    First, the goal of this analysis is to peform a KM Survival Analysis. I have searched here and the articles pertaining to that analysis don't quite...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Ncf5031 ,

    Please try below steps:

    1. add two measure with below dax formula

    RK =
RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[EQUIP_HRS] ) ),, ASC )

    ALIVE_AT_Time =
VAR ctn =
    COUNTROWS ( ALL ( 'Table' ) )
VAR cur_tk = [RK]
VAR tmp =
    FILTER ( ALL ( 'Table' ), [RK] <= cur_tk )
VAR num =
    SUMX ( tmp, [FAILURE_AT_TIME] )
RETURN
    ctn - num

    2. add a table visual with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ncf5031's avatar
Ncf5031
Icon for Advocate I rankAdvocate I
3 years ago

Anonymous thanks for the reply. What I am needing is "running total" (using subtraction) of the number of pieces of equipment that are currently still operating at any given point in time.

 

So based on the example table above, there are 5 total pieces of equipment. The first piece fails at an hour count of "blank" (represents a null value), so the "Time At Alive" value is now 4. Another piece fails at 0 hours, so there are now 3 pieces "alive", another piece fails at 50 hours, so now there are 2 pieces "alive", so on a so forth.

 

I have a list of all hour counts that equipment failed at, and the number of pieces of equipment that failed at that hour count ("Failure At Time"). The missing piece now is this "Alive At Time" column which is the running total via subtraction. I currently have 800 rows in my table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Ncf5031 ,

    Please try below steps:

    1. add two measure with below dax formula

    RK =
RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[EQUIP_HRS] ) ),, ASC )

    ALIVE_AT_Time =
VAR ctn =
    COUNTROWS ( ALL ( 'Table' ) )
VAR cur_tk = [RK]
VAR tmp =
    FILTER ( ALL ( 'Table' ), [RK] <= cur_tk )
VAR num =
    SUMX ( tmp, [FAILURE_AT_TIME] )
RETURN
    ctn - num

    2. add a table visual with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.