Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
My tasks if to calculate total customers into groups based on the total orders they made.
For example:
Total customers who made from 1 to 5 orders.
Total customers who made from 6 to 10 orders.
Total customers who made from 11+ orders.
What is the formula to group these 3 customers segmentation like this?
In this case, I use the sample_superstore.
Hope you could help me! Thanks so much
I made this customers segment in Tableau, but I dont know how to make it in Power Bi. This is the way I made in Tableau.
1. Calculate Orders 1 - 5
2. Caculate number of Total Customers who made 1-5 orders.
Thanks for reading! and hope to hear from you!!
Solved! Go to Solution.
Hi again @Anonymous
I updated my PBIX with the same dataset you have used in your Tableau workbook.
My existing measure is actually sufficient to do what you want.
I created the same visual you had in Tableau:
Date of First Purchase = CALCULATE ( MIN ( Orders[Order Date] ), ALLEXCEPT ( Orders, Orders[Customer ID] ) )Note that I hid this column in Report View
Months Since First Purchase = VAR MonthIndex = DATEDIFF ( Orders[Date of First Purchase], Orders[Order Date], MONTH ) RETURN "M" & FORMAT ( MonthIndex, "00" )
You could also create measures that apply particular segment filters, such as
Number Customer Purchase 1 to 5 = CALCULATE ( [Number of Customers by Order Count Segment], Segment[Segment Name] = "1 to 5" )
PBIX attached. Please post back if needed.
Regards,
Owen
Note that when including both Months Since First Purchase and First Purchase Month on the visual , all customers have five or fewer orders.
@OwenAuger Sorry for once again disturbing you!
I just look at the power bi of Order Count again. The number of Customer of 1-5 change, but for 6-10 and 11+ it is not available in the visual
Maybe it would be my error at some points.
Could you help me please to check it, whether I made error at some points?
Here is the power bi file: https://we.tl/t-LOBm6PdKL3https://we.tl/t-Qk16HJsrPG
Hope to hear from you!!
Thanks!!
Hi @Anonymous
Just got to looking at this - not sure what the current status is given the other replies.
The reason you don't see anything in the 6-10 or 11+ segments in that particular visual is that no customers have that many orders for any one value of "months since first purchase".
Did you want the segmentation to ignore the filter on "months since first purchase"? If so, the segmentation measure would need to be re-written to ignore that filter when segmenting a customer.
Also, as far as I can tell the filtering is working. The segmentation will be recalculated based on whatever other filters are applied. Can you give an example of what wasn't working.
Regards,
Owen
Dear @OwenAuger
Once again, I would like to thank you and sorry for my mistaken.
That's true, there is no customer purchase from 6-10 in 1st purchase, that's why it is not available on dashboard.
Hi,
I answered a similar question here - Segment customers into dynamic buckets.
Hope this helps.
Hi,
I am sorry but i do not know what you mean by that.
Dear Ashish,
Thank you for your reply.
It almost answer my question but not exactly what I look for.
Here is the link that contain my Tableau workbook and the expected result that I want.
Please have a look at that.
Thank you and hope to hear from you!!
Hi,
First, I set a measure called Order 1-5 by IF statement
Then, for each customers who has order 1-5, let count the number of customers.
That is my formlar, if you need workbook Tablaeau, I can send it to you!!
Thanks
Thanks for the explanation.
Yes, if you could provide a link to the Tableau workbook, that might make it easier to ensure we're producing the same thing in Power BI.
The existing measure I posted does count the number of customers within each segment, but does so within the current filter context. We might need to modify this measure to ignore certain filters when deciding which segment a customer is in.
I will take a closer look at what you've done in Tableau first.
Regards,
Owen
Dear Owen,
Thank you for your reply.
Here is the workbook in tableau and the result the I expect also.
Please have a look at that
Thank you and hope to hear from you soon.
Hi again @Anonymous
I updated my PBIX with the same dataset you have used in your Tableau workbook.
My existing measure is actually sufficient to do what you want.
I created the same visual you had in Tableau:
Date of First Purchase = CALCULATE ( MIN ( Orders[Order Date] ), ALLEXCEPT ( Orders, Orders[Customer ID] ) )Note that I hid this column in Report View
Months Since First Purchase = VAR MonthIndex = DATEDIFF ( Orders[Date of First Purchase], Orders[Order Date], MONTH ) RETURN "M" & FORMAT ( MonthIndex, "00" )
You could also create measures that apply particular segment filters, such as
Number Customer Purchase 1 to 5 = CALCULATE ( [Number of Customers by Order Count Segment], Segment[Segment Name] = "1 to 5" )
PBIX attached. Please post back if needed.
Regards,
Owen
Note that when including both Months Since First Purchase and First Purchase Month on the visual , all customers have five or fewer orders.
Hi,
I just applied it to data.
it gives me the same result, nothing changed.
Could you help me to check it, please?
thanks
Hi,
I am trying to interpret that data there. Please let me know if my understanding is correct
Is my understanding correct?
Hi,
The things that you said is correct.
But i tried the solution above, though it seems to be work, but it does not work. so if you have any solution,
please help me!!
Thanks
Hi,
Hasn't OwenAuger solved your problem? Also, my the solution at my link also solves the problem.
Thank you for your reply,
I just checked the result and I dont know why there something wrong here. It maybe from my mistakes.
If you have time, you could have a look at it and help me to fix it.
I also looked at your fomular, it almost the same that I want, but the thing I really want is to make the Cohort Analysis of Customer Segmentation!!
here is the link to the power bi workbook!! https://we.tl/t-Qk16HJsrPG
Thanks!!
Hi,
There is no file there.
Hi,
Whil ei may be able to build the attrition table for you, i wll not be able to dynamically segment that attrition table. This is becuase there cannot be a relationship (active or inactive) between the Orders Table and the Segment Table. So, i may have to write 3 measures - one each for the 3 segments.
Also, in the Matrix visual that i build, i will have to show Years and Months in the column headers instead of M00, M01 etc.
Dear @Ashish_Mathur
Thank you for your reply.
Could you provide me the power bi file so that I could have a detailed formula and your solutions?
Thanks!!
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |