Forum Discussion

NOVICE02's avatar
NOVICE02
Icon for Helper III rankHelper III
4 years ago
Solved

Divide value by another in same column - for cohort analysis

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

 

monthColumn_monthCancelled_volumeCancel %
202011Activated7328 
20201120-Nov3084%
20201120-Dec1903%
20201121-Jan831%
20201121-Feb541%
20201121-Mar1452%
20201121-Apr1021%
20201121-May90%
20201121-Jun90%
20201121-Jul30%
20201121-Aug561%
20201121-Sep511%
20201121-Oct491%
20201121-Nov90%
20201121-Dec40%
202012Activated636 
20201220-Dec30.5%
Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    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/

     

     

2 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    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/

     

     

  • NOVICE02's avatar
    NOVICE02
    Icon for Helper III rankHelper III
    4 years ago

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