topic Re: Count orders of customer and create segment in DAX Commands and Tips

Count orders of customer and create segment

clarissa — Thu, 25 Jun 2020 07:58:12 GMT

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?

Re: Count orders of customer and create segment

Anonymous — Thu, 25 Jun 2020 08:45:22 GMT

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)

Re: Count orders of customer and create segment

clarissa — Thu, 25 Jun 2020 11:15:12 GMT

Thank you.

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

Re: Count orders of customer and create segment

Anonymous — Thu, 25 Jun 2020 11:16:29 GMT

Hi clarissa ,

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

Regards,

Re: Count orders of customer and create segment

clarissa — Thu, 25 Jun 2020 11:19:38 GMT

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?

Re: Count orders of customer and create segment

Anonymous — Thu, 25 Jun 2020 11:29:59 GMT

Hi clarissa ,

Do Emails repaeat in you tble ?

Can you share some 10 like of sample?

Regards,

Re: Count orders of customer and create segment

clarissa — Thu, 25 Jun 2020 11:34:06 GMT

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

Re: Count orders of customer and create segment

Anonymous — Thu, 25 Jun 2020 11:46:09 GMT

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)