Calculate monthly churn rate on different dimension

[romina80]

Hello,

I'm very new to PowerBi and I'm struggling to find the best solution to calculate a monthly churn rate on different dimensions.

I have my dataset as follows:

Month	Contract status	movement type	actual
Jan-25	OOC	Voluntary	10
Jan-25	IC	Voluntary	2
Jan-25	OOC	Ports	15
Jan-25	IC	Ports	3
Jan-25	OOC	Closing Base	100
Jan-25	IC	Closing Base	300

and I want to build a trend report to show the churn rate by contract status and by movement type.

The calculation should be:

Month	Contract status	Churn rate	Formula
Jan-25	OOC	0.25	(10+15)/100
Jan-25	IC	0.016666667	(2+3)/300
Month	Movement type	Churn rate	Formula
Jan-25	Voluntary	0.03	(10+2)/(100+300)
Jan-25	Ports	0.045	(15+3)/(100+300)

Is there an easy way to calculate or do I need to modify my dataset and try to bring the Closing Base values in the same rows as the churn values?

Thanks for the help

[AthmakuriRekha]

Hi @romina80 ,

Here is my solution. 

I have created 2 tables, one has ports and voluntry filtered data and other has closing base. Please find below dax queries. Created new custom column to establish relationship combining month and contractstatus.

Dax 1: 

let
Source = Sql.Database(server, dbname),
dbo_churn = Source{[Schema="dbo",Item="churn"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_churn, each ([movementtype] <> "Closing Base")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each [MONTH]+[Contractstatus])
in
#"Added Custom"

Dax 2 :

let
Source = Sql.Database(server,dbname),
dbo_churn = Source{[Schema="dbo",Item="churn"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_churn, each ([movementtype] = "Closing Base")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each [MONTH]+[Contractstatus])
in
#"Added Custom"

Now create table in the report and add a custom function to calculate ChurnRate as below. From Home tab add new visual calculation add custom function. 

similarly calculate the movement type churnrate as well as below.

Please accept the solution if this answers your question. Let us know if still have any concerns.
Thanks !!

[bhanu_gautam · Accepted solution]

@romina80 

You need to create calculated columns to separate the churn values and the closing base values.

ChurnValue = IF(
[movement type] IN {"Voluntary", "Ports"},
[actual],
BLANK()
)

 Create a calculated column to identify closing base values:

ClosingBaseValue = IF(
[movement type] = "Closing Base",
[actual],
BLANK()
)

Create measures to calculate the churn rate.

DAX
TotalChurnByContractStatus = CALCULATE(
SUM('Table'[ChurnValue]),
ALLEXCEPT('Table', 'Table'[Month], 'Table'[Contract status])
)

DAX
TotalClosingBaseByContractStatus = CALCULATE(
SUM('Table'[ClosingBaseValue]),
ALLEXCEPT('Table', 'Table'[Month], 'Table'[Contract status])
)

DAX
ChurnRateByContractStatus = DIVIDE(
[TotalChurnByContractStatus],
[TotalClosingBaseByContractStatus]
)

DAX
TotalChurnByMovementType = CALCULATE(
SUM('Table'[ChurnValue]),
ALLEXCEPT('Table', 'Table'[Month], 'Table'[movement type])
)

DAX
TotalClosingBase = CALCULATE(
SUM('Table'[ClosingBaseValue]),
ALLEXCEPT('Table', 'Table'[Month])
)

DAX
ChurnRateByMovementType = DIVIDE(
[TotalChurnByMovementType],
[TotalClosingBase]
)

Churn Rate by Contract Status:

Create a table or chart visual.
Add Month, Contract status, and ChurnRateByContractStatus to the visual.
Churn Rate by Movement Type:

Create a table or chart visual.
Add Month, movement type, and ChurnRateByMovementType to the visual.

[romina80]

Hello @bhanu_gautam ,

thanks for the help your solution is working fine.

I have another challenge because the churn rate has to be calculated using the average between the current month and previous month closing base. I will do another post for that.