How can I do a segmentation in order to understand who are the customers I have segmented?

[garluc93]

I have a Detail Sales Report with customer number id, date of order, type of order ,sales number id ecc

I need to segment my clients in POWERBI in order of clients who order more than 2/3 per year, more than 1/3 per year and more than 0. At the end I have to find new customers. This process is enough easy in excel where I have done a pivot and then a boolean table if the client order or not in a month.. then the sum ecc Do you have an idea?

I have created a new table Test = SUMMARIZECOLUMNS('Detail Sales Report'[Fiscal Year];'Detail Sales Report'[Fiscal Month Number];'Detail Sales Report'[Customer Number];"quantity";SUMX(FILTER('Detail Sales Report';'Detail Sales Report'[SalesFoc]="SALES"&&'Detail Sales Report'[Total Invoiced Units]>=0);'Detail Sales Report'[Total Invoiced Units]))

[v-juanli-msft]

Hi @garluc93 

Could you share an example data of your original excel data?

Your formula logic is:

1. if a user have no oder in previous year, then for the first month of this year, he is new,

2. If a user  in 2016/2 have orders from previous year(2015/1/1) to 2016/1/31,

 2.1 if his orders is less than 1/3(what is this, how can i calculate this?), then he is regarded as spot,

 2.2 if his orders is more than 1/3, but less than 2/3, he is regared as silver,

 2.3 if his orders is more than 2/3,  he is regared as glod,

Right?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[garluc93]

Hi @v-juanli-msft 

A client is gold in january if the previous 3 months the sum of booleans ( if(units>0;1;0) divided for 3 is  >0,68. I have finded new customers with a groupby for customers with min and max date of buying for each customer. It's hard to find a gold customer: I have done this new table with summarizedcoloumns. Do you think is it a good start?