Forum Discussion
Help - Customer Categorization in selected periods
Hi, I am new to Power BI and need help on below.
I have data at date and custome level with channel type. I would need to have 2 period(date) paramenters and categorize customers based on selected dates in period parameters. Below is the sample data and expected output.
| Customer | Date | Channel | Parameters | From | To | |||
| A | 1-Oct | On | P1 | 1-Oct | 2-Oct | |||
| A | 2-Oct | Off | P2 | 3-Oct | 3-Oct | |||
| A | 3-Oct | Dir | ||||||
| A | 4-Oct | On | Output | |||||
| B | 1-Oct | On | P1 | P2 | Dir | Off | Total | |
| B | 2-Oct | Off | On,Off | 1 | 1 | 2 | ||
| B | 3-Oct | Off | Dir | 0 | 1 | 1 | ||
| C | 2-Oct | Dir | Total | 1 | 2 | |||
| C | 3-Oct | Off |
in the above example, Oct 1 - Oct 2 is selected for P1 (Period 1), so Customer A will fall into On,Off category,Customer B into On,Off and Customer C into Dir for P1. Similar calculation for P2 cateogries and then matrix of these 2 with count of customers. I am not able to find a way to create these calculations, any help is much appreciated.
Thanks in advance.
5 Replies
Hi Anonymous ,
You can create a new calculated column to determine parameter for each customer by date:
parameter = SWITCH(TRUE(),'Table'[Date]>=DATE(2020,10,1) &&'Table'[Date]<=DATE(2020,10,2),"P1",'Table'[Date]=DATE(2020,10,3),"P2")Then you can create a matrix for your expected output:
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EV4vA8GQfvZKhJexAEPZeEEBPxh0xugnaN36TCNPOdUCww?e=ccvw1L
If this post help, please consider accept it as the solution to help other member find it more quickly.
Best Regards,
Dedmon Dai
Hi v-deddai1-msft ,
Thank you so much for your response, but requirement is bit different. I need to divide customers into different sets based on their shopping channel for both period 1 and period 2. For example if customer shopped in both online and offline for the selected period p1, his P1 label need to fixed at customer level to online & offline. I need to group customers into different combinations (Customers shopped on only online, online+offline, only offline etc ..) for both period 1 and period 2 parameters.
Hi Anonymous ，
Would you please try to create two calculated column :
P = SWITCH(TRUE(),'Table'[Date]>=DATE(2020,10,1)&&'Table'[Date]<=DATE(2020,10,2),"P1",'Table'[Date] = DATE(2020,10,3),"P2") combinations = CONCATENATEX(FILTER('Table','Table'[Customer] = EARLIER('Table'[Customer])&&'Table'[P] = EARLIER('Table'[P])),'Table'[Channel],"&")Then you can create a measure for count customers in different combinations :
Measure = DISTINCTCOUNT('Table'[Customer])For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EctwMJyl_SlIlaH8QUDWjDkBozN9GGPqSQH4JwBdWSAtGw?e=WIcDJq
If this post help, please consider accept it as the solution to help other member find it more quickly.
Best Regards,
Dedmon Dai
Hi v-deddai1-msft ,
Thank you so much for the help. Calculations looks perfect except that the date filter in the calculation needs to be dynamic based on selection by user. We need 2 date parameters (P1 & P2) for user to select different periods. Then based on these period selection at run time, we need to calculate combinations. Could you please help ..
Thank you.
Hi Anonymous ，
I can't think of a solution for applying dynamic categeory on column. They must be fixed in the formula based on my knowledge. Maybe someone else could give you a solution.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
