Pie chart grouping customers by frequency cluster

[Anonymous]

Hello,

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

I have this database as example of mine:

Client ID	Status	Datetime
A	Validated	10/09/2018
A	Cancelled	11/09/2018
A	Validated	12/09/2018
B	Validated	10/09/2018
B	Validated	11/09/2018
B	Validated	12/09/2018
B	Validated	13/09/2018
B	Validated	14/09/2018
B	Validated	15/09/2018
C	Cancelled	19/09/2018
C	Validated	20/09/2018
C	Cancelled	21/09/2018
C	Cancelled	22/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:

Client	Validated bookings
A	2
B	6
C	1

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:

Could someone help me with that?

Thanks a lot,

Lucas

[v-danhe-msft]

Hi @Anonymous,

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

[Anonymous]

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!

[v-danhe-msft]

Hi @Anonymous,

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")))

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"))

Create a "Both" relationship with the two tables:

Now you could get the correct result:

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

Regards,

Daniel He

[Anonymous]

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!

[Anonymous]

Hello @v-danhe-msft,

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

Thanks a lot!

[v-danhe-msft]

Hi @Anonymous,

Have you created the relationships between the two tables?

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

Regards,

Daniel He

[JoyCornerstone]

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.