Count orders of customer and create segment

[clarissa]

Hi

I have customer-email adresses and order numbers as different columns. I'd like to calculate the number of orders per customer-email and then create a separate column "segment" based on the number of orders. 

The table should be able to show segments with then the connected net sales (same table).

How to achieve this?

[harshnathani]

Hi @clarissa ,

Please share sample data and the expected output te help you on this.

Regards,

HN

[clarissa]

ok

I have the following data:

email cst.       order_no               

......                  abc

......                  efg

........                hijk

- I need to count the distinct order-No per e-mail cst

- I need an addition column to create segments based on number of orders, such as:

Segment = IF(

DISTINCTCOUNT(sales[order_no]) = 1,

"new",

IF(

DISTINCTCOUNT(sales[order_no]) = 2,

"repeat",

"loyal")

)

- I need to be able to show the values by segment

Does that help?

[harshnathani]

Hi @clarissa ,

Create a measure

DC = CALCULATE(DISTINCTCOUNT('Table'[Order No]),ALLEXCEPT('Table','Table'[email]))

Then create a New Table

Seg =

ADDCOLUMNS(

SUMMARIZE(

'Table',

'Table'[email],

"Distinct_", [DC]),

"Segment",

SWITCH(

TRUE(),

[Distinct_] = 1, "New",

[Distinct_] = 2, "Repeat",

"Loyal"

))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani]

Hi @clarissa ,

Do Emails repaeat in you tble ?

Can you share some 10 like of sample?

Regards,

HN

[clarissa]

Unfortunately I can't share the table, but the email is also not unique... so an e-mail address can come up a few times with different order numbers... can you work with this?  Thank you so much 

[harshnathani]

Hi @clarissa ,

You can try something like this

https://radacad.com/customers-grouped-by-count-of-their-orders-static-segmentation-in-power-bi

Do share sample data incase this does not work.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[clarissa]

Thank you.

Do you have a solution with a formula for measures? like summarize and group by?