Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
Hi I actually used this other solution as I didn't get to filter by date with this one:
But thanks a lot for your advise!
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
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!
Hello @v-danhe-msft,
Actually, the date filter doesn't changes the graphic result, do you how can I integrate this?
Thanks a lot!
Hi @Anonymous,
Have you created the relationships between the two tables?
Based on my test, it could work on my side:
Regards,
Daniel He
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.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |