Divide value by another in same column - for cohort analysis

[NOVICE02]

Hi all,

I have aggregated data for cohort analysis ( as the transactional layer is too large) and it looks like below. I aggregated using sql in a way so that visually when i apply the column month , it will show the activated cohort first and then the other months will represent the volumes that cancelled. this is the layout that is needed

What i want to do is show the Cancel % over the activated volume grouped by month

is this possible?

so 20-nov = 308/7328

20 dec = 190/7328

and so on

month	Column_month	Cancelled_volume	Cancel %
202011	Activated	7328
202011	20-Nov	308	4%
202011	20-Dec	190	3%
202011	21-Jan	83	1%
202011	21-Feb	54	1%
202011	21-Mar	145	2%
202011	21-Apr	102	1%
202011	21-May	9	0%
202011	21-Jun	9	0%
202011	21-Jul	3	0%
202011	21-Aug	56	1%
202011	21-Sep	51	1%
202011	21-Oct	49	1%
202011	21-Nov	9	0%
202011	21-Dec	4	0%
202012	Activated	636
202012	20-Dec	3	0.5%

[NOVICE02]

Thank you so much Vahid. This is exactly what i needed 🙂

[VahidDM · Accepted solution]

Hi @NOVICE02 

Try this code to add a new column to your table:

Cancel% = 
VAR _Ac =
    CALCULATE (
        MAX ( 'Table'[Cancelled_volume] ),
        FILTER ( ALL ( 'Table' ), 'Table'[month] = EARLIER ( 'Table'[month] ) )
    )
RETURN
    IF ( 'Table'[Column_month] = "Activated", BLANK (), [Cancelled_volume] / _Ac )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/