cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lucas_pbi Frequent Visitor
Frequent Visitor

Pie chart grouping customers by frequency cluster

Hello,

 

I'm new in Power BI and I need some help with the following:

 

I have this database as example of mine:

 

Client IDStatusDatetime
AValidated10/09/2018
ACancelled11/09/2018
AValidated12/09/2018
BValidated10/09/2018
BValidated11/09/2018
BValidated12/09/2018
BValidated13/09/2018
BValidated14/09/2018
BValidated15/09/2018
CCancelled19/09/2018
CValidated20/09/2018
CCancelled21/09/2018
CCancelled22/09/2018

 

 

So I want to count how many bookings each customer did and then group it in clusters of less than 5 bookings, between 5 and 10, etc.

 

At the moment, I only get to do a table with each customer and the number of bookings:

ClientValidated bookings
A2
B6
C1

 

 

But I want to show this in a pie chart with a slicer where I can filter the date (ex.: all the bookings in September) where Group 1 would be less than 5 bookings with 2 customers in it, Group 2 (>5 and <10) with 1 customer and Group 3 (>10) with no custumers:

image.png

Could someone help me with that?

 

Thanks a lot,

 

Lucas

7 REPLIES 7
JoyCornerstone Regular Visitor
Regular Visitor

Re: Pie chart grouping customers by frequency cluster

When I did this, I created a table that was like:

 

Name           Min       Max

Group A          0            5

Group B          6          10

Group C         11        1000000

 

Then I did a lookup to see what group they were in.  This worked well for us.

Community Support Team
Community Support Team

Re: Pie chart grouping customers by frequency cluster

Hi @lucas_pbi,

Based on my test, you could refer to below steps:

Create a new table(Table1 is your row data):

New Table = SUMMARIZE('Table1','Table1'[Client ID],"Validated bookings",CALCULATE(COUNT(Table1[Client ID]),FILTER('Table1','Table1'[Status]="Validated")))

1.PNG

Create a calculated column in the new table:

Group = IF('New Table'[Validated bookings]<=5,"Group 1",IF('New Table'[Validated bookings]<10,"Group 2","Group 3"))

1.PNG

Create a "Both" relationship with the two tables:

1.PNG

Now you could get the correct result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lucas_pbi Frequent Visitor
Frequent Visitor

Re: Pie chart grouping customers by frequency cluster

Hello @v-danhe-msft,

 

I'm trying your solution, thanks for the answer.

 

One question about it, when I use the datetime filter, does it update my SUMMARIZE function and regroup customers according to that?

 

Thanks a lot!

lucas_pbi Frequent Visitor
Frequent Visitor

Re: Pie chart grouping customers by frequency cluster

Hello @v-danhe-msft,

 

Actually, the date filter doesn't changes the graphic result, do you how can I integrate this?

 

Thanks a lot!

Community Support Team
Community Support Team

Re: Pie chart grouping customers by frequency cluster

Hi @lucas_pbi,

Have you created the relationships between the two tables?

2.PNG

Based on my test, it could work on my side:

1.PNG

 

Regards,

Daniel He

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

Re: Pie chart grouping customers by frequency cluster

Hi @lucas_pbi,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lucas_pbi Frequent Visitor
Frequent Visitor

Re: Pie chart grouping customers by frequency cluster

Hi I actually used this other solution as I didn't get to filter by date with this one:

 

https://community.powerbi.com/t5/Desktop/Pie-chart-grouping-customers-by-number-of-appearences-in/m-...

 

But thanks a lot for your advise!