Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello PowerBI Community,
I have managed to pull the raw data I need and have begun plotting on a matrix table for a cohort analysis. The issue I'm facing is that I cannot seem to work out how to make the figures cumulative... i.e February 2017 should be 3 in month 1, 3 in month 2, 3 in month 4 etc..... then 5 in month 13 etc. In essence, the values are counting the amount of user_ids joining at a particular month. Each Month/Year is the cohort upon which new sales members joined the team (we have had new people every month).
So far I think I have this.... but by partitioning by cohort, I'm puzzled about the filters...
Cumulative Values =
CALCULATE (
SUM((DISTINCTCOUNT('Cohorting Table'[user id])
),
FILTER (
cohort = x, month >= y????
Thanks!
@dsabsi @amitchandak - might you be able to help?
@Greg_Deckler - I saw you made a simple to understand calc here - https://community.powerbi.com/t5/DAX-Commands-and-Tips/Cumulative-Running-Total/m-p/1080236 - I'm struggling to use it though!
Solved! Go to Solution.
Hi @Anonymous
Please see if the following helps you. As part of the solution, I have made some data transformations, created relationships, and a measure. All these changes can be seen in the pbix. I have used the excel files you had send me. So, you can just change the file path of the source files, should you wish to refresh or explore the query editor part.
PFA the pbix file also.Download PBIX file
@Anonymous , Need some data.
No month on top should be diff what. Today() - Start Date or first Date and Start date (it is column name )
I have retails data
First Sales = minx(filter(Retail, Retail[Customer_id]=EARLIER(Retail[Customer_id])),Retail[Order_Date])
Month Since first Sales = DATEDIFF(Retail[First Sales],Retail[Order_Date],MONTH)
Now I join first sales with date table take month -year on row
Join the second with series and take on column
Month Of Sales = ADDCOLUMNS(GENERATESERIES(0,100), "Month Seq", "Month" & [Value])
In case we need a measure bucketing , Assume Age is my measure
Age bucket Count = CALCULATE(COUNTX(filter(VALUES(Retail[Customer_id]), [Age] =max('Month Of Sales'[Month No])),Retail[Customer_id]),CROSSFILTER('Month Of Sales'[Month No],Retail[Month Since first Sales],None))
crossfilter is option
also refer
https://finance-bi.com/power-bi-cohort-analysis/
http://funbiworld.com/2019-12-02-cohort-analysis/
https://www.youtube.com/watch?v=tuk1CrigTSI
Hi @amitchandak ,
Thanks for your reply - what specific data do you need?
Well the month/year tell me what month the sales cohort onboarded a client. So in February 2017, the sales cohort onboarded 3 clients in the first month and then in their 13th month, they onboarded a further 2 clients. It goes all the way to 42 months (as thats the month difference from Today() to the sales joined date.
I'm not sure I understand your workings in that case!
S
@Anonymous Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi @Greg_Deckler ,
Many thanks.
1) Please find sample data below in the URL: https://docs.google.com/spreadsheets/d/1PTz8ElfVvYAfxbudrMSXHEn_j1_33rtv8P4dU3CsNGE/edit?usp=sharing
2) The expected output is mentioned in the original post.
3) I currently have a table with the cohort of sales people (month and year) on the left and the month that they have been with us on the top (0-42). I have already plotted the (distinct) count of user_id's on each month, however I would like to make these numbers cumulative. This is because the cohort analysis table will show us how many sales to date they have made throughout their time with us.
I hope this makes sense and thanks again for your time in responding.
@amitchandak - does the above help? Thanks!
@Anonymous , I will check that. I have given a webinar on a similar topic.
refer: https://youtu.be/Q1vPWmfI25o?t=752
Thanks @amitchandak - I have created a date table and made a join. This is my calculation so far for the values:
Distinct Count of Clients =
CALCULATE (
DISTINCTCOUNT ( 'Cohorting Table'[Client User ID] ),
USERELATIONSHIP ( _Dates[Date], 'Cohorting Table'[First Salesman Application Completed Date] )
)
The trick now is to make it cumulative... 🙂
Hi @Anonymous
Please see if the following helps you. As part of the solution, I have made some data transformations, created relationships, and a measure. All these changes can be seen in the pbix. I have used the excel files you had send me. So, you can just change the file path of the source files, should you wish to refresh or explore the query editor part.
PFA the pbix file also.Download PBIX file
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |