Advanced Cohort Analysis

[danzrust]

Hi guys,

I am looking at customer renewals rates "after" the month 0 while also showing the month 0 as 100% and all that in one metric.

I have a dimension which tells me which month after the first purchase the actual order was made.... So:

00 months 

01 months

02 months 

and so on up to 17 months.

"00 months" is the month of the first order. 

What I'm trying to achieve is the following:

I want to show the absolute count of orders in the "00 months" as 100%. That's basically cohort size.
AND I want to show all other numbers as (orders in "XXth month" always divided by cohort size).... basically % renewal rate for the given XXth month since purchase. 

The result should look like this:

Currently, I'm at the stage where I have absolute numbers for each XX month and I am wondering whether the above shown visualization is actually possible to bundle into 1 metric...?

The reason why I want to do this craziness in 1 metric is to be able to use table wide color-coding...

I tried to mess around with SWITCH and SUMX but to no avail...

Any bright ideas?

DZ

[amitchandak]

@danzrust , refer to this video, where I did something similar or to the video

https://www.youtube.com/watch?v=Q1vPWmfI25o

blog

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...