group customer by orders

[Anonymous]

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

[Ashish_Mathur]

Hi,

Check my reply dates July 6.  There is a download link there.

[Ashish_Mathur]

Whom are you replying to?

[Anonymous]

@Ashish_Mathur I forgot to tag you in the post!! Sorry!

[Anonymous]

Thank you for helping me to solve the problem!!

Really appricated!! 

[OwenAuger]

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:

1. Load the Orders table
2. Create a Segment table that looks like:
3. 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] )
   )

4. Create a visual grouped by Segment Name with this measure:

   This measure will naturally respond to any other filters applied.

Regards,

Owen

[MAAbdullah_47]

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.

[OwenAuger]

@MAAbdullah_47  

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?

[MAAbdullah_47]

@OwenAuger 

It gives only the number of Customers , Not No Of orders , got it? 

[MAAbdullah_47]

Hi @OwenAuger  
I couldn't , could you show me a screen-shot please ? 

[Anonymous]

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