Retention Rate in Matrix

[Anonymous]

Hi,

I have a matrix viz that looks like this to check the retention month over month:

	1	2	3	4	5
Jan	120	119	118	115	110
Feb	280	280	280	260	250
Mar	180	150	120	90	50

Rows: First Giving Date

Columns: Index

Values: Count of AccountIDs

For example, we had 120 accounts who started the subscription in January. The 2nd month, 1 person canceled the subscription, so we have 119 accounts.

Now, I want to see this in percentage.

	1	2	3	4	5
Jan	100%	99%	98%	96%	92%
Feb	100%	100%	100%	93%	89%
Mar	100%	83%	67%	50%	28%

I tried this in DAX, but it is not giving me the correct result.

VAR accounts_per_index = COUNT(AccountID)

VAR all_accounts = CALCULATE(COUNT(AccountID), FILTER(AccountTable, AccountTable[Index]=1))

Can someone help me with getting the correct result?

Thank you so much!

[Anonymous]

Hello @Anonymous ,

We can achieve the desired result with the use of the DAX Measure.

For the test purpose, I created a dummy table with your values aggregated in one column like this.

Then I simply create one measure and plot it into the matrix with the month and index columns and the result I got is like and also plot another matrix with column values only for comparison:

And the used measure is : 

Thanks,
Neel

[Anonymous · Accepted solution]

Hi Neel,

Thanks for your response. In my case, I might need to drill down based on account information, so I didn't create a summary table like you provided. I ended up using below formula and it worked.

All Records = CALCULATE(COUNT('Account & Giving'[AccountId]),'Account & Giving'[Index]=1)

Retention Rate = COUNT('Account & Giving'[AccountId])/'Account & Giving'[All Records]