Pie chart grouping customers by number of appearences in database and time filtering

[Anonymous]

Hello,

I have the following example of my database (see table below) where I want to count the number of times a customer was validated, then group it by clusters (ex.: less than 5 times, between 5 and 10, more than 10). 

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

At the moment, I can do a table with the clients and number of bookings validated, filtered by date, like that:

Client	Frequency
A	2
B	6
C	1

But what I want is to show it in a pie chart with how many customers are in each cluster and the possibility to filter by date.

So at the end I want to have this pie chart with the % or number of customers that are in each category for the month of September for example :

Please could someone help me with that?

Thanks a lot,

Lucas

[v-shex-msft · Accepted solution]

HI @Anonymous,

I'd like to suggest you add calculated column to group your records with frequency label, then write a measure to calculate validated records count.

Calculate column:

Clusters = 
VAR frequency =
    COUNTROWS (
        FILTER (
            ALL ( Table1 ),
            [Client ID] = EARLIER ( Table1[Client ID] )
                && [Status] = "Validated"
                && FORMAT ( [Datetime], "mm/yyyy" )
                    = FORMAT ( EARLIER ( Table1[Datetime] ), "mm/yyyy" )
        )
    )
RETURN
    IF (
        frequency > 10,
        "More than 10",
        IF (
            frequency > 5 && frequency < 10,
            "between 5 to 10",
            IF ( frequency < 5, "less than 5" )
        )
    )

Measure:

Validated count = 
CALCULATE (
    COUNT ( Table1[Datetime] ),
    VALUES ( Table1[Client ID] ),
    Table1[Status] = "Validated"
)

Regards,

Xiaoxin Sheng

[Anonymous]

The solution works fine! Thanks a lot! 😃