Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

order 1-5.png

2. Caculate number of Total Customers who made 1-5 orders. 

Number Customers 1-5.png

 

Thanks for reading! and hope to hear from you!! 

1 ACCEPTED 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:

  1. 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
  2. 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" )
  3. Applied a Page Level Filter, Segment Name = "1 to 5"
  4. 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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

30 REPLIES 30
Anonymous
Not applicable

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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. 

 

 

Ashish_Mathur
Super User
Super User

Hi,

I answered a similar question here - Segment customers into dynamic buckets.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur , can I get the resources?

Hi,

I am sorry but i do not know what you mean by that.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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. 

https://we.tl/t-3SjZ91cTEM

Please have a look at that. 

Thank you and hope to hear from you!!

 

Anonymous
Not applicable

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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. 

https://we.tl/t-3SjZ91cTEM

 

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:

  1. 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
  2. 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" )
  3. Applied a Page Level Filter, Segment Name = "1 to 5"
  4. 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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Hi, 

I just applied it to data. 

it gives me the same result, nothing changed. 

Could you help me to check it, please?

thanks 

same result.PNG

Hi,

I am trying to interpret that data there.  Please let me know if my understanding is correct

  • For the Jan 2010 row, the figure 141 means that these many people bought the product  in Jan 2010 itself
  • For the Jan 2010 row, the figure 6 (appearing under M1) means that of the 141 people who bougth this product in Jan 2010, 6 bought the product in Feb 2010 as well
  • For the Jan 2010 row, the figure 4 (appearing under M2) means that of the 141 people who bougth this product in Jan 2010, 6 bought the product in March 2010 as well

Is my understanding correct?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

https://we.tl/t-LOBm6PdKL3

Dear @Ashish_Mathur 

here it is. Sorry for the fail of connection

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.