Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |