Cohorts Analysis - Working on the Total Row

[AvnerShier]

Hi Communaty,

I have analyze Customer cohort retention as you can see above but i have problem with the "Total" row --

Instad of taking the customers in Preiod 7 (for example) and devide it by the equivalnt in preiod 0 it dividing it by ALL customers from preiod 0 and make the total inaccurate - i would love to know what i need to change in code to make it work.

Thanks  a lot !!!

Avner

[Fowmy]

@AvnerShier 

Better if you can share a sample PBIX file.

Modify the last line in your formula as follows:

IF ( not isfiltered(Account_Snapshot[Cohort Group]),
[numberCustomers],
//last line as in your formula//
)

[AvnerShier]

Hi @Fowmy  Thanks a lot!!! i implemented this line but now it gives me 100% on all the Total.

Can you tell me what i did wrong (unfotenatly i cant share the PBIX becouse it sensative data with 1M+ Rows

Customer Retention % =

var selectedFutureMonth = SELECTEDVALUE(Account_Snapshot[Cohort Period])

var maxRank = MAXX(ALL(Account_Snapshot[Activation_Month].[Date]),[Activation_Month].[Date])

var months = VALUES(Account_Snapshot[Activation_Month].[Date])

var monthsFiltered = FILTER(months, Account_Snapshot[Activation_Month].[Date]<=EDATE(maxRank,-selectedFutureMonth))

RETURN SUMX(monthsFiltered,

[NumCustomers])

/

SUMX(monthsFiltered,

IF ( not isfiltered(Account_Snapshot[Cohort Group]),[NumCustomers],CALCULATE([NumCustomers],ALL(account_Snapshot[Cohort Period]),Account_Snapshot[Cohort Period]=0)))

[Fowmy]

@AvnerShier 

Can you create a sample PBIX file that represents your data and share it?

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.