Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey guys,
Hope you are keeping well!
Can you please help me on this topic:
Made a cohort table (as pivot table from the data set) that looks like this
Sum of MRR | MonthsActive | ||||
Row Labels | 1 | 2 | 3 | 4 | 5 |
2020-02 | 18,960 | 18,960 | 9,480 | 9,480 | 9,480 |
2020-03 | 18,480 | 14,460 | 18,480 | 10,440 | 18,480 |
2020-04 | 7,200 | 7,200 | 7,200 | 7,200 | 7,200 |
2020-06 | 13,200 | 13,200 | 13,200 | 13,200 | 13,200 |
2020-07 | 9,480 | 9,480 | |||
2020-09 | 15,384 | 17,064 | 17,064 | 17,064 | 17,064 |
2020-10 | 8,040 | 8,040 | 8,040 | 8,040 | 8,040 |
2020-11 | 6,834 | 6,834 | 6,834 | 6,834 | 6,834 |
2020-12 | 24,440 | 24,440 | 24,440 | 24,440 | 24,440 |
2021-03 | 37,100 | 35,948 | 35,948 | 35,948 | 35,948 |
2021-05 | 20,876 | 20,876 | 27,968 | 27,968 | |
2021-06 | 10,610 | 10,610 | 10,610 |
and made a measure that is calculating retention per month active that works like this
Retention%=
DIVIDE (
SUM ( [MRR] ),
CALCULATE ( SUM ( [MRR] ), [MonthsActive] = 1, VALUES ( [Cohort] ) )
)
that gives me this result
MonthsActive | |||||
Row Labels | 1 | 2 | 3 | 4 | 5 |
Retention% | 100% | 98.17% | 94.05% | 94.19% | 94.02% |
but the result is somehow wrong: months 1,2,3 are calculated good, but 4 and 5 are wrong
The results should be
1 | 2 | 3 | 4 | 5 | |
Retention% | 100.00% | 98.17% | 94.05% | 89.23% | 88.42% |
and the reason is that formula I have, excludes cohort 2020-07 from the beginning sum.
Hope there is some possible improvement in the formula so it can calculate it correctly.
Please help.
Many thanks!
Does adding ALL ( DimDate[Year-Month] ), assuming that's the appropriate column name, as another argument in CALCULATE help?
Are your months coming from a date dimension table or the same table as [MMR]?
If you don't have a separate dimension table, you might be running into an Auto-Exist issue.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |