Power BI Practice Solution ROUND #6 I Analyze your data by COHORT
VloĆŸit
- Äas pĆidĂĄn 30. 07. 2024
- Topline numbers are often hiding underlying trends that can be revealed once you analyze your data by cohort. In this Power BI Practice Round we break apart the customers (create cohorts) based on the year of first purchase.
Requirements:
- Create an area chart that shows total sales over time by year and quarter
- Add a measure that calculates the total sales for new customers
While there does not seem to be a problem with the top level sales, the sales for new customers is going down. To get more insights you have to break down revenue by different customer cohorts.
- Create a calculated column that returns the first year of purchase.
- Create an area chart that shows total sales over time by year and quarter
- Insert a 100% stacked column chart. Show years on the axis, total sales on values, and year of first order on the legend
- Create an area chart that shows total sales over time by year and quarter
- Create another cohort based on the Number of Orders per Customer. Do this using a calculated column
- Insert a stacked column chart with Number of Orders per Customers on the x-axis and the distinct count of customers on values. Place the year of first order on the legend.
Share your approach, questions, feedback below or on Twitter using the hashtag #PowerBIPractice and tag @HowToPowerBI
Download file here datatraining.io/powerbi-how-to
--------------------------------
đ TRAININGS đ
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
đ JOIN đ
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGOâ
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
đ CHECK THIS OUT! đ
---------------------------------
đ» My gear amzn.to/47F21Yc
đ Power BI books MUST READ! amzn.to/3tUfFcj
đĄ General books I recommend amzn.to/48YNo33
đ¶ Music for my videos www.epidemicsound.com/referra...
đ For growing on CZcams: www.tubebuddy.com/bas
đ Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! đȘ đ
#HowToPowerBIâ #PowerBIâ #DataTrainingâ
#powerbidesktopâ #powerbitrainingâ #powerbideveloperâ #DAX - VÄda a technologie
Thanks Bas, this is exactly what I was looking for. To the point and easy to understand.
Another great video. Thank you for sharing !!!
Thanks a lot!
Great video... I am just starting my power bi journey and all these "extra" explanations helped me a lot (ie: why I need the calculate to create the column)... Looking forward seeing next video!
Glad it was helpful! next one coming up soon
Really outstanding explanation...
Hey Bas, nice video.
If you needed to avoid calculated columns, how would you approach this?
you just saved me bro thanx alot
Well done Bas! When I was making your example I did not have this option categorical/continuous. Later I saw it in the options.
Thx Bart! If the X-axis is string/Boolean, the Type option will not display. Maybe you initially had a different field on the x axis and therefore it didnt show.
I had no idea the new customer's pattern was now a quick measure. đ
yea, easy to overlook that one! :D
Very interesting and useful and insightful analysis, thanks
Glad to hear this Abeer, thanks for watching
Hi Bas, Great explaination, one quick doubt
at 05:35 , cant the same column be achieved with this formula
YearOfFirstOrder = year(FIRSTDATE(Sales[Order Date]))
I used it and it gives the same result. Then I guess we don't have to use RELATEDTABLE function here.
Thanks, Bas. This video is great and very informative. But I have a question about the 2nd chart: total sales by Year, with breakdown legend of Year of First Order. I wonder instead of using Year of 1st Order, can you use Customer Age/Tenure as the breakdown legend? This will be dynamic according to the Year / Timeline, so for the customers with 1st orders in 2015, their tenure will be 0, 1, 2, 3 respectively in 2015 to 2018. I think using customer tenure / age group as the legend has many uses, like checking the views of your new and old subscribers across time. I have tried but failed to create such a legend. I wonder whether you have some ideas?
Hi Bas - For the last graph, I don't find the type of graph with value - I only have those with X and Y axis - is it possible to tell us which one we have to pick ? thanks
Use Y-axis.
enjoying all the practices, Bas.
for YearOfFirstOrder, why not just this?
YEAR(CALCULATE(MIN(Sales[Order Date])))
not sure why RELATED is not required. but it gives the same answers. or is this slower?
đźthat does look easier and more efficient! next time I need to run the solution by you first for a double check haha đ thanks for pointing out!
@@HowtoPowerBI haha you flatter me. I just think simple because I only know simple. I have another one for round 7 too.
i love u ar exercises please we need more
Hi! It was awesome, but could you please make âclassicâ cohort analysis using matrix. I still canât find any normal solution (((
Very Informative, Thanks Bas!
great to hear! thxđ
Where is the dataset ?
download link is in the description