Forum Discussion
group customer by orders
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!!
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:
- Created First Purchase Date calculated column in Orders and related this to a First Purchase Date table:
Date of First Purchase = CALCULATE ( MIN ( Orders[Order Date] ), ALLEXCEPT ( Orders, Orders[Customer ID] ) )Note that I hid this column in Report View - Created Monts Since First Purchase calculated columns:
Months Since First Purchase = VAR MonthIndex = DATEDIFF ( Orders[Date of First Purchase], Orders[Order Date], MONTH ) RETURN "M" & FORMAT ( MonthIndex, "00" ) - Applied a Page Level Filter, Segment Name = "1 to 5"
- Placed my earlier measure Number of Customers by Order Count Segment in a matrix visual, with some conditional formatting similar to your Tableau example.
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.
- Created First Purchase Date calculated column in Orders and related this to a First Purchase Date table:
30 Replies
Hi Anonymous
In this situation, with Power BI you would typically use a Dynamic Segmentation pattern
(see here).I have attached a sample PBIX using a version of the Superstore data.
The steps are:
- Load the Orders table
- Create a Segment table that looks like:
- Create a measure Number of Customers by Order Count Segment
following the pattern from the above link (more-or-less):
Number of Customers by Order Count Segement = IF ( ISFILTERED ( Segment[Segment Name] ), COUNTROWS ( FILTER ( VALUES ( Orders[Customer ID] ), VAR OrderCount = CALCULATE ( DISTINCTCOUNT ( Orders[Order ID] ) ) RETURN COUNTROWS ( FILTER ( Segment, ( OrderCount >= Segment[Min Value] || ISBLANK ( Segment[Min Value] ) ) && ( OrderCount <= Segment[Max Value] || ISBLANK ( Segment[Max Value] ) ) ) ) > 0 ) ), DISTINCTCOUNT ( Orders[Customer ID] ) ) - Create a visual grouped by Segment Name with this measure:
This measure will naturally respond to any other filters applied.
Regards,
Owen
Dear,
Thank you for your solution.
Could you please to spend time to make the customer segmentation into separated group?
Like I did in Tableau, I calculated total number of different group of customer who made orders from 1-5, 6-10, 10+
In this case, you dont need to separate into B2B or C2C. just number of customer from each orders segmentation.
I would use this to build cohort analysis like this:
Thanks for help!!
Your solution OwenAuger is excllent but have one small problem, when you clcik on the segment it doesn't give the number of orders for each segment , please look at the screen-shot below:
Segment of orders
For Example if I click on 1 to 5 Segment the card doesn't give the total no of order of this particuler segment (gives the total orders in all segments) , please advise.
If you place the same "segmented" measure on the card as is used on the other visual, you should see the segmented value there as well.
Does that work in your case?
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 Ashish_Mathur , can I get the resources?
Hi,
I am sorry but i do not know what you mean by that.
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.
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.
