topic DAX Calculation of Survival Rates in DAX Commands and Tips

DAX Calculation of Survival Rates

khesan99 — Fri, 07 Oct 2022 02:06:04 GMT

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.

RowIndex	Number of Plants Planted	Number of Plants Died	Plant Death Rate	Survival Rates
0	282	28	9.9%	100.0%
1	314	25	8.0	90.1
2	290	6	2.1	82.9
3	303	12	4.0	81.2
4	300	13	4.3	78
5	292	7	2.4	74.6
6	254	28	11.0	72.8

Thanks in advance for your help.

Re: DAX Calculation of Survival Rates

lbendlin — Fri, 07 Oct 2022 23:11:07 GMT

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".

Re: DAX Calculation of Survival Rates

khesan99 — Sat, 08 Oct 2022 03:57:25 GMT

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