5 Replies Latest reply on May 24, 2018 2:35 AM by Chris Hastie

# Weighted avg calculation

Hello dear community,

I'm trying to achieve something calculated using Excel via Tableau. The calculation is basically this 'SUMPRODUCT(A1:A5;B1:B5)/SUM(B1:B5)' to calculate the weighted average of returning customers for a giving cohort.

My excel table looks like the attached picture.

I started by calculating the %of returning users (Table calculation of percent from first()) and when I wanted to multiply this % it got messy.

Anyone already did this or knows how to achieve this ?

• ###### 1. Re: Weighted avg calculation

Hi Ayoub,

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] )

/

SUM([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?

Cheers,

Chris

InterWorks

• ###### 2. Re: Weighted avg calculation

Hey Chris,

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 ?

• ###### 3. Re: Weighted avg calculation

Hey Ayoub,

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.

Cheers,

Chris

InterWorks

• ###### 4. Re: Weighted avg calculation

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% .

• ###### 5. Re: Weighted avg calculation

Hi Ayoub,

How does the attached look?

Cheers,

Chris

InterWorks