Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
AvnerShier
Regular Visitor

Cohorts Analysis - Working on the Total Row

Cohort Analysis.jpg

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

@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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.