Create a percentage measure based on first value of a group to be used as the total

[suebayes]

I am creating a cohort analysis of some transactional data.  I've grouped by the first month that the purchase was made and then created order periods for repeated purchases by customers from the first month cohort.  I am trying to track and give measures for retention of customers.  I can show the numbers each month for each cohort, total spend and average spend.  I want to be able to show the retention as a percentage of the first month.

So for cohort 2019-06 it % for order period 2109-06 would be 41/41

For cohort 2019-05, order period would be 2019-05 would be 80/80, for order period 2019/06 would be 5/80 and so forth.  I've tried all sorts but got my head in a pickle.  Any help gratefully received.

Sue

[sturlaws]

Hi, @suebayes 

you can solve this by changing the cohort and order period columns to dates, and use a calculated column like this:

Percentage retention =
DIVIDE (
    cohorts[count];
    CALCULATE (
        SUM ( cohorts[count] );
        FILTER (
            ALL ( cohorts );
            cohorts[Cohort] = EARLIER ( cohorts[Cohort] )
                && cohorts[order period] = cohorts[Cohort]
        )
    )
)

[suebayes · Accepted solution]

Perfect, I hadn't come back to this until now and it works brilliantly. thank you