cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

## Cohorts Analysis - Working on the Total Row

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

3 REPLIES 3
Super User

@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//
)

Did I answer your question? Mark my post as a solution! and hit thumbs up
Regular Visitor

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)))
Super User

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

Did I answer your question? Mark my post as a solution! and hit thumbs up