Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I need some help writing a measure to calculate the percentage distribution of "leave grouping" (staff availability) .
A sample of the data is included in a reply to this post.
A calendar used to group the data by week is in this spoiler.
calendar_date | Week grouping | Week sort |
1/11/22 | -2 | 2 |
2/11/22 | -2 | 2 |
3/11/22 | -2 | 2 |
4/11/22 | -2 | 2 |
5/11/22 | -2 | 2 |
6/11/22 | -2 | 2 |
7/11/22 | -1 | 3 |
8/11/22 | -1 | 3 |
9/11/22 | -1 | 3 |
10/11/22 | -1 | 3 |
11/11/22 | -1 | 3 |
12/11/22 | -1 | 3 |
13/11/22 | -1 | 3 |
14/11/22 | 0 | 4 |
15/11/22 | 0 | 4 |
16/11/22 | 0 | 4 |
17/11/22 | 0 | 4 |
18/11/22 | 0 | 4 |
19/11/22 | 0 | 4 |
20/11/22 | 0 | 4 |
21/11/22 | +1 | 5 |
22/11/22 | +1 | 5 |
23/11/22 | +1 | 5 |
24/11/22 | +1 | 5 |
25/11/22 | +1 | 5 |
26/11/22 | +1 | 5 |
27/11/22 | +1 | 5 |
28/11/22 | +2 | 6 |
29/11/22 | +2 | 6 |
30/11/22 | +2 | 6 |
1/12/22 | +2 | 6 |
2/12/22 | +2 | 6 |
3/12/22 | +2 | 6 |
4/12/22 | +2 | 6 |
5/12/22 | +3 | 7 |
6/12/22 | +3 | 7 |
7/12/22 | +3 | 7 |
8/12/22 | +3 | 7 |
9/12/22 | +3 | 7 |
10/12/22 | +3 | 7 |
11/12/22 | +3 | 7 |
12/12/22 | +4 | 8 |
13/12/22 | +4 | 8 |
14/12/22 | +4 | 8 |
15/12/22 | +4 | 8 |
16/12/22 | +4 | 8 |
17/12/22 | +4 | 8 |
18/12/22 | +4 | 8 |
19/12/22 | +5 | 9 |
20/12/22 | +5 | 9 |
21/12/22 | +5 | 9 |
22/12/22 | +5 | 9 |
23/12/22 | +5 | 9 |
24/12/22 | +5 | 9 |
25/12/22 | +5 | 9 |
26/12/22 | +6 | 10 |
27/12/22 | +6 | 10 |
28/12/22 | +6 | 10 |
29/12/22 | +6 | 10 |
30/12/22 | +6 | 10 |
31/12/22 | +6 | 10 |
Which have a relationship by date.
I'm hoping a measure could be created to calculate a distribution percentage of "leave grouping" on a per unit level, then on the visual side I can use additional filters as needed.
Which can then be used to make separate visuals like:
Branch C | 79% |
Branch A | 78% |
Branch E | 75% |
Branch B | 66% |
Branch D | 63% |
Unit 12 | 21% |
Unit 14 | 16% |
Unit 3 | 15% |
Unit 7 | 13% |
Unit 1 | 12% |
Any help you could provide would be appreciated!
Sample Staff Availability data
Branch | Unit | Full Name | Date | Data entered | Status | Leave Grouping |
A | U2 | 21 | 2/11/22 | Entered | Available | Available |
A | U2 | 19 | 2/11/22 | Entered | Available | Available |
A | U4 | 29 | 2/11/22 | Entered | Available | Available |
A | U2 | 21 | 4/11/22 | Entered | Available | Available |
A | U2 | 19 | 4/11/22 | Entered | Available | Available |
A | U4 | 29 | 4/11/22 | Entered | Available | Available |
A | U2 | 21 | 8/11/22 | Entered | Available | Available |
A | U2 | 19 | 8/11/22 | Entered | Available | Available |
A | U4 | 29 | 8/11/22 | Entered | Available | Available |
A | U2 | 21 | 10/11/22 | Entered | Available | Available |
A | U2 | 19 | 10/11/22 | Entered | Available | Available |
A | U4 | 29 | 10/11/22 | Entered | Available | Available |
A | U2 | 19 | 14/11/22 | Entered | Available | Available |
A | U2 | 21 | 14/11/22 | Entered | Available | Available |
A | U4 | 29 | 14/11/22 | Entered | Available | Available |
D | U5 | 37 | 14/11/22 | Entered | Unavailable | Non-work day |
A | U2 | 21 | 16/11/22 | Entered | Unavailable | Leave unplanned |
A | U2 | 19 | 16/11/22 | Entered | Available | Available |
A | U4 | 29 | 16/11/22 | Entered | Available | Available |
D | U5 | 37 | 16/11/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 18/11/22 | Entered | Available | Available |
A | U2 | 21 | 18/11/22 | Entered | Available | Available |
A | U4 | 29 | 18/11/22 | Entered | Available | Available |
D | U5 | 37 | 18/11/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 22/11/22 | Entered | Available | Available |
A | U2 | 21 | 22/11/22 | Entered | Available | Available |
A | U4 | 29 | 22/11/22 | Entered | Available | Available |
D | U5 | 37 | 22/11/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 24/11/22 | Entered | Available | Available |
A | U2 | 21 | 24/11/22 | Entered | Available | Available |
A | U4 | 29 | 24/11/22 | Entered | Available | Available |
D | U5 | 37 | 24/11/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 28/11/22 | Entered | Available | Available |
A | U2 | 21 | 28/11/22 | Entered | Available | Available |
A | U4 | 29 | 28/11/22 | Entered | Unavailable | Leave planned |
D | U5 | 37 | 28/11/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 30/11/22 | Entered | Available | Available |
A | U2 | 21 | 30/11/22 | Entered | Available | Available |
A | U4 | 29 | 30/11/22 | Entered | Available | Available |
D | U5 | 37 | 30/11/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 2/12/22 | Entered | Available | Available |
A | U2 | 21 | 2/12/22 | Entered | Available | Available |
A | U4 | 29 | 2/12/22 | Entered | Available | Available |
D | U5 | 37 | 2/12/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 6/12/22 | Entered | Available | Available |
A | U2 | 21 | 6/12/22 | Entered | Available | Available |
A | U4 | 29 | 6/12/22 | Entered | Unavailable | Leave planned |
D | U5 | 37 | 6/12/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 8/12/22 | Entered | Available | Available |
A | U2 | 21 | 8/12/22 | Entered | Available | Available |
A | U4 | 29 | 8/12/22 | Entered | Unavailable | Leave planned |
D | U5 | 37 | 8/12/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 12/12/22 | Entered | Available | Available |
A | U2 | 21 | 12/12/22 | Entered | Available | Available |
A | U4 | 29 | 12/12/22 | Entered | Unavailable | Leave planned |
D | U5 | 37 | 12/12/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 14/12/22 | Entered | Available | Available |
A | U2 | 21 | 14/12/22 | Entered | Available | Available |
A | U4 | 29 | 14/12/22 | Entered | Unavailable | Leave planned |
D | U5 | 37 | 14/12/22 | Entered | Unavailable | Non-work day |
A | U2 | 21 | 16/12/22 | Entered | Available | Available |
A | U2 | 19 | 16/12/22 | Entered | Unavailable | Leave planned |
A | U4 | 29 | 16/12/22 | Entered | Unavailable | Leave planned |
D | U5 | 37 | 16/12/22 | Entered | Unavailable | Non-work day |
A | U2 | 21 | 20/12/22 | Entered | Available | Available |
A | U2 | 19 | 20/12/22 | Entered | Unavailable | Leave planned |
A | U4 | 29 | 20/12/22 | Entered | Available | Available |
D | U5 | 37 | 20/12/22 | Entered | Unavailable | Non-work day |
A | U2 | 21 | 22/12/22 | Entered | Available | Available |
A | U2 | 19 | 22/12/22 | Entered | Unavailable | Leave planned |
A | U4 | 29 | 22/12/22 | Entered | Available | Available |
D | U5 | 37 | 22/12/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 26/12/22 | Entered | Unavailable | Public Holiday |
A | U2 | 21 | 26/12/22 | Entered | Unavailable | Public Holiday |
A | U4 | 29 | 26/12/22 | Entered | Unavailable | Public Holiday |
A | U4 | 37 | 26/12/22 | Entered | Unavailable | Public Holiday |
D | U5 | 37 | 26/12/22 | Entered | Unavailable | Public Holiday |
A | U2 | 19 | 28/12/22 | Entered | Unavailable | Leave planned |
A | U2 | 21 | 28/12/22 | Entered | Unavailable | Leave planned |
A | U4 | 29 | 28/12/22 | Entered | Unavailable | Non-work day |
A | U4 | 37 | 28/12/22 | Entered | Unavailable | Non-work day |
D | U5 | 37 | 28/12/22 | Entered | Unavailable | Non-work day |
A | U2 | 19 | 30/12/22 | Entered | Unavailable | Leave planned |
A | U2 | 21 | 30/12/22 | Entered | Unavailable | Leave planned |
A | U4 | 29 | 30/12/22 | Entered | Unavailable | Non-work day |
A | U4 | 37 | 30/12/22 | Entered | Unavailable | Non-work day |
D | U5 | 37 | 30/12/22 | Entered | Unavailable | Non-work day |
A | U4 | 37 | 2/11/22 | No | Available | Available |
D | U5 | 37 | 2/11/22 | No | Available | Available |
A | U4 | 37 | 4/11/22 | No | Available | Available |
D | U5 | 37 | 4/11/22 | No | Available | Available |
A | U2 | 19 | 6/11/22 | No | Available | Available |
A | U2 | 21 | 6/11/22 | No | Available | Available |
A | U4 | 29 | 6/11/22 | No | Available | Available |
A | U4 | 37 | 6/11/22 | No | Available | Available |
D | U5 | 37 | 6/11/22 | No | Available | Available |
A | U4 | 37 | 8/11/22 | No | Available | Available |
D | U5 | 37 | 8/11/22 | No | Available | Available |
A | U4 | 37 | 10/11/22 | No | Available | Available |
D | U5 | 37 | 10/11/22 | No | Available | Available |
A | U2 | 19 | 12/11/22 | No | Available | Available |
A | U2 | 21 | 12/11/22 | No | Available | Available |
A | U4 | 29 | 12/11/22 | No | Available | Available |
A | U4 | 37 | 12/11/22 | No | Available | Available |
D | U5 | 37 | 12/11/22 | No | Available | Available |
A | U4 | 37 | 14/11/22 | No | Available | Available |
A | U4 | 37 | 16/11/22 | No | Available | Available |
A | U4 | 37 | 18/11/22 | No | Available | Available |
A | U2 | 19 | 20/11/22 | No | Available | Available |
A | U2 | 21 | 20/11/22 | No | Available | Available |
A | U4 | 29 | 20/11/22 | No | Available | Available |
A | U4 | 37 | 20/11/22 | No | Available | Available |
D | U5 | 37 | 20/11/22 | No | Available | Available |
A | U4 | 37 | 22/11/22 | No | Available | Available |
A | U4 | 37 | 24/11/22 | No | Available | Available |
A | U2 | 19 | 26/11/22 | No | Available | Available |
A | U2 | 21 | 26/11/22 | No | Available | Available |
A | U4 | 29 | 26/11/22 | No | Available | Available |
A | U4 | 37 | 26/11/22 | No | Available | Available |
D | U5 | 37 | 26/11/22 | No | Available | Available |
A | U4 | 37 | 28/11/22 | No | Available | Available |
A | U4 | 37 | 30/11/22 | No | Available | Available |
A | U4 | 37 | 2/12/22 | No | Available | Available |
A | U2 | 19 | 4/12/22 | No | Available | Available |
A | U2 | 21 | 4/12/22 | No | Available | Available |
A | U4 | 29 | 4/12/22 | No | Available | Available |
A | U4 | 37 | 4/12/22 | No | Available | Available |
D | U5 | 37 | 4/12/22 | No | Available | Available |
A | U4 | 37 | 6/12/22 | No | Available | Available |
A | U4 | 37 | 8/12/22 | No | Available | Available |
A | U2 | 19 | 10/12/22 | No | Available | Available |
A | U2 | 21 | 10/12/22 | No | Available | Available |
A | U4 | 29 | 10/12/22 | No | Available | Available |
A | U4 | 37 | 10/12/22 | No | Available | Available |
D | U5 | 37 | 10/12/22 | No | Available | Available |
A | U4 | 37 | 12/12/22 | No | Available | Available |
A | U4 | 37 | 14/12/22 | No | Available | Available |
A | U4 | 37 | 16/12/22 | No | Available | Available |
A | U2 | 19 | 18/12/22 | No | Available | Available |
A | U2 | 21 | 18/12/22 | No | Available | Available |
A | U4 | 29 | 18/12/22 | No | Available | Available |
A | U4 | 37 | 18/12/22 | No | Available | Available |
D | U5 | 37 | 18/12/22 | No | Available | Available |
A | U4 | 37 | 20/12/22 | No | Available | Available |
A | U4 | 37 | 22/12/22 | No | Available | Available |
A | U2 | 19 | 24/12/22 | No | Available | Available |
A | U2 | 21 | 24/12/22 | No | Available | Available |
A | U4 | 29 | 24/12/22 | No | Available | Available |
A | U4 | 37 | 24/12/22 | No | Available | Available |
D | U5 | 37 | 24/12/22 | No | Available | Available |
A | U4 | 37 | 28/12/22 | No | Available | Available |
A | U4 | 37 | 30/12/22 | No | Available | Available |
Hi @om3ga ,
Please share some sample data and expceted result.
Best Regards,
Jay
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |