You should be able to calculate your weighted average in a similar fashion to your Excel calculation, using the following formula:
SUM( [Retention Rate] * [Cohort Size] )
You attached image shows the retention rates as figures in a table, however your comment regarding table calculations leads me to suspect that these figures are calculated? If so, I would expect your final solution to use some form of Level of Detail Expressions.
Are you able to share either a sample dataset or a copy of your calculation for retention rate?
Thank you for your prompt reply.
The retention rate is also calculated in the workbook. Here's how : ZN([nbr_users]) / LOOKUP(ZN([nbr_users]), FIRST())
And the cohort size is equal to : COUNTD([user_id])
Does that help ?
That does help. I've mocked up a possibility in the attached workbook. Without access to a sample workbook I can't be sure if I'm doing the right thing, however my hope is that this sparks the missing piece for you. My main suggestion is to calculate Retention Rate using an EXCLUDE LOD calculation instead of a LOOKUP table calc.
Please take a look and let me know your thoughts.
Thanks again Chris.
That's also what I could achieve, however, the requirements are different ; during the months where there's no full data (younger cohorts) the weighted average should be based on the columns with available data only.
Coming back to your sample workbook, in the weighted average of the 5th active month It should be 12% instead of 1% .