Customer Churn Decile Formula

[FrederickRoxas]

Can you please help to create a Power BI Formula for Customer Churn Decile (0-1)

Churn Decile	Count	Churn Rate
	2,490,571	32.11%
	826,774	10.66%
	465,130	6.00%
	349,451	4.51%
	297,777	3.84%
	261,467	3.37%
	242,771	3.13%
	257,794	3.32%
	325,898	4.20%
	1,906,946	24.59%
	331,157	4.27%
Total	7,755,736	100.00%

[FrederickRoxas]

Kudos

[Sahir_Maharaj · Accepted solution]

Hello @FrederickRoxas,

Can you please try this:

1. Rank Customers Based on Churn Probability

Churn Rank = RANKX(ALL(Customers), Customers[ChurnProbability],, DESC, Dense)

2. Assign Decile Based on Rank

Decile = 
VAR TotalCustomers = COUNTROWS(Customers)
VAR DecileSize = TotalCustomers / 10
RETURN CEILING(Customers[Churn Rank] / DecileSize, 1)

3. Calculate Churn Rate for Each Decile

Decile Churn Rate = 
DIVIDE(
    SUMX(
        FILTER(Customers, Customers[Decile] = SELECTEDVALUE(Customers[Decile])),
        Customers[HasChurned]
    ),
    COUNTROWS(FILTER(Customers, Customers[Decile] = SELECTEDVALUE(Customers[Decile]))),
    BLANK()
)

 Hope this helps!

[FrederickRoxas]

Thank you so much.