Forum Discussion

khesan99's avatar
khesan99
Regular Visitor
3 years ago
Solved

DAX Calculation of Survival Rates

Hi

I am relatively new to DAX and have a problem that is causing me trouble.  This is not the actual scenario for confidentiality reasons.

I need to calculate the [Survival_Rate].

We are a nursery that plants a number of plants each month.  And each month there are a number of plants that die. 

I need to calculate [Survival_Rate] over a period of 30 months (could be days, months or years, this is purely an index column).  The [Survival_Rate] is for each month what percentage are still alive.  For example at Month 20 there are  250 plants alive and 20 died.

The [Plant Death Rate] is calculated by [Number Plants Died]/[Number of Plants Planted]
Survival_Rate is to be calculated as follows:

  • The [Survival_Rate] for the [RowIndex] = 0  is 100
  • For [RowIndex] = 1, it is 100*(1-[PlantDeathRate] from RowIndex=0)
  • For [RowIndex] = 2, it is  [Result From RowIndex = 1 above) * (1-[PlantDeathRate from RowIndex = 1])

 

The table below has some sample data with the correct calculations.

RowIndexNumber of Plants PlantedNumber of Plants DiedPlant Death RateSurvival Rates
0282289.9%100.0%
1314258.090.1
229062.182.9
3303124.081.2
4300134.378
529272.474.6
62542811.072.8

Thanks in advance for your help.

  • lbendlin's avatar
    lbendlin
    3 years ago

    DAX is not necessarily the right tool for that, although you can use PRODUCTX to some extent.  Better to do this in Power Query,

     

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "LY3JEcAwCAN74e2HOewktTDuv41EO/lo0CJBt00bFnegdkabf2N6CSwAu0e5jU8FptQDUAAFPAGLhnoXfnOs/h/nBQ==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [RowIndex = _t, #"Number of Plants Planted" = _t, #"Number of Plants Died" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"RowIndex", Int64.Type},
      {"Number of Plants Planted", Int64.Type},
      {"Number of Plants Died", Int64.Type}
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Plant Death Rate",
    each [Number of Plants Died] / [Number of Plants Planted],
    type number
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom",
    "Survival Rate",
    each List.Accumulate(
      {0 .. [RowIndex]},
      100,
      (state, current) =>
        if current = 0 then state else state * (1 - #"Added Custom"[Plant Death Rate]{current - 1})
    ),
    type number
  )
in
  #"Added Custom1"

     

     

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

     

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    DAX is not necessarily the right tool for that, although you can use PRODUCTX to some extent.  Better to do this in Power Query,

     

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "LY3JEcAwCAN74e2HOewktTDuv41EO/lo0CJBt00bFnegdkabf2N6CSwAu0e5jU8FptQDUAAFPAGLhnoXfnOs/h/nBQ==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [RowIndex = _t, #"Number of Plants Planted" = _t, #"Number of Plants Died" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"RowIndex", Int64.Type},
      {"Number of Plants Planted", Int64.Type},
      {"Number of Plants Died", Int64.Type}
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Plant Death Rate",
    each [Number of Plants Died] / [Number of Plants Planted],
    type number
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom",
    "Survival Rate",
    each List.Accumulate(
      {0 .. [RowIndex]},
      100,
      (state, current) =>
        if current = 0 then state else state * (1 - #"Added Custom"[Plant Death Rate]{current - 1})
    ),
    type number
  )
in
  #"Added Custom1"

     

     

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

     

     

  • khesan99's avatar
    khesan99
    Regular Visitor
    3 years ago

    wow, thanks Iblendin, this is awesome !  Sincere thanks also to pointing out that DAX was not the best idea.