Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
om3ga
Frequent Visitor

Calculate percentage by category with a filter

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.

Spoiler
calendar_dateWeek groupingWeek sort
1/11/22-22
2/11/22-22
3/11/22-22
4/11/22-22
5/11/22-22
6/11/22-22
7/11/22-13
8/11/22-13
9/11/22-13
10/11/22-13
11/11/22-13
12/11/22-13
13/11/22-13
14/11/2204
15/11/2204
16/11/2204
17/11/2204
18/11/2204
19/11/2204
20/11/2204
21/11/22+15
22/11/22+15
23/11/22+15
24/11/22+15
25/11/22+15
26/11/22+15
27/11/22+15
28/11/22+26
29/11/22+26
30/11/22+26
1/12/22+26
2/12/22+26
3/12/22+26
4/12/22+26
5/12/22+37
6/12/22+37
7/12/22+37
8/12/22+37
9/12/22+37
10/12/22+37
11/12/22+37
12/12/22+48
13/12/22+48
14/12/22+48
15/12/22+48
16/12/22+48
17/12/22+48
18/12/22+48
19/12/22+59
20/12/22+59
21/12/22+59
22/12/22+59
23/12/22+59
24/12/22+59
25/12/22+59
26/12/22+610
27/12/22+610
28/12/22+610
29/12/22+610
30/12/22+610
31/12/22+610

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:

  • Percentage of "staff available for duties" this week by branch* 
    Branch C79%
    Branch A78%
    Branch E75%
    Branch B66%
    Branch D63%
  • Top 5 units with the most staff unavailable to work this week*
    Unit 1221%
    Unit 1416%
    Unit 315%
    Unit 713%
    Unit 112%
    *Note: "this week" filter will be added to the visual from the calendar table and "top 5" filter could be added on the visual level.
  • So statements like this could be made "this week Branch C had 79% of staff available for duties." or "21% of staff in Unit 12 are not available to work this week"

Any help you could provide would be appreciated!

2 REPLIES 2
om3ga
Frequent Visitor

@v-jayw-msft 

Sample Staff Availability data

Spoiler
BranchUnitFull NameDateData enteredStatusLeave Grouping
AU2212/11/22EnteredAvailableAvailable
AU2192/11/22EnteredAvailableAvailable
AU4292/11/22EnteredAvailableAvailable
AU2214/11/22EnteredAvailableAvailable
AU2194/11/22EnteredAvailableAvailable
AU4294/11/22EnteredAvailableAvailable
AU2218/11/22EnteredAvailableAvailable
AU2198/11/22EnteredAvailableAvailable
AU4298/11/22EnteredAvailableAvailable
AU22110/11/22EnteredAvailableAvailable
AU21910/11/22EnteredAvailableAvailable
AU42910/11/22EnteredAvailableAvailable
AU21914/11/22EnteredAvailableAvailable
AU22114/11/22EnteredAvailableAvailable
AU42914/11/22EnteredAvailableAvailable
DU53714/11/22EnteredUnavailableNon-work day
AU22116/11/22EnteredUnavailableLeave unplanned
AU21916/11/22EnteredAvailableAvailable
AU42916/11/22EnteredAvailableAvailable
DU53716/11/22EnteredUnavailableNon-work day
AU21918/11/22EnteredAvailableAvailable
AU22118/11/22EnteredAvailableAvailable
AU42918/11/22EnteredAvailableAvailable
DU53718/11/22EnteredUnavailableNon-work day
AU21922/11/22EnteredAvailableAvailable
AU22122/11/22EnteredAvailableAvailable
AU42922/11/22EnteredAvailableAvailable
DU53722/11/22EnteredUnavailableNon-work day
AU21924/11/22EnteredAvailableAvailable
AU22124/11/22EnteredAvailableAvailable
AU42924/11/22EnteredAvailableAvailable
DU53724/11/22EnteredUnavailableNon-work day
AU21928/11/22EnteredAvailableAvailable
AU22128/11/22EnteredAvailableAvailable
AU42928/11/22EnteredUnavailableLeave planned
DU53728/11/22EnteredUnavailableNon-work day
AU21930/11/22EnteredAvailableAvailable
AU22130/11/22EnteredAvailableAvailable
AU42930/11/22EnteredAvailableAvailable
DU53730/11/22EnteredUnavailableNon-work day
AU2192/12/22EnteredAvailableAvailable
AU2212/12/22EnteredAvailableAvailable
AU4292/12/22EnteredAvailableAvailable
DU5372/12/22EnteredUnavailableNon-work day
AU2196/12/22EnteredAvailableAvailable
AU2216/12/22EnteredAvailableAvailable
AU4296/12/22EnteredUnavailableLeave planned
DU5376/12/22EnteredUnavailableNon-work day
AU2198/12/22EnteredAvailableAvailable
AU2218/12/22EnteredAvailableAvailable
AU4298/12/22EnteredUnavailableLeave planned
DU5378/12/22EnteredUnavailableNon-work day
AU21912/12/22EnteredAvailableAvailable
AU22112/12/22EnteredAvailableAvailable
AU42912/12/22EnteredUnavailableLeave planned
DU53712/12/22EnteredUnavailableNon-work day
AU21914/12/22EnteredAvailableAvailable
AU22114/12/22EnteredAvailableAvailable
AU42914/12/22EnteredUnavailableLeave planned
DU53714/12/22EnteredUnavailableNon-work day
AU22116/12/22EnteredAvailableAvailable
AU21916/12/22EnteredUnavailableLeave planned
AU42916/12/22EnteredUnavailableLeave planned
DU53716/12/22EnteredUnavailableNon-work day
AU22120/12/22EnteredAvailableAvailable
AU21920/12/22EnteredUnavailableLeave planned
AU42920/12/22EnteredAvailableAvailable
DU53720/12/22EnteredUnavailableNon-work day
AU22122/12/22EnteredAvailableAvailable
AU21922/12/22EnteredUnavailableLeave planned
AU42922/12/22EnteredAvailableAvailable
DU53722/12/22EnteredUnavailableNon-work day
AU21926/12/22EnteredUnavailablePublic Holiday
AU22126/12/22EnteredUnavailablePublic Holiday
AU42926/12/22EnteredUnavailablePublic Holiday
AU43726/12/22EnteredUnavailablePublic Holiday
DU53726/12/22EnteredUnavailablePublic Holiday
AU21928/12/22EnteredUnavailableLeave planned
AU22128/12/22EnteredUnavailableLeave planned
AU42928/12/22EnteredUnavailableNon-work day
AU43728/12/22EnteredUnavailableNon-work day
DU53728/12/22EnteredUnavailableNon-work day
AU21930/12/22EnteredUnavailableLeave planned
AU22130/12/22EnteredUnavailableLeave planned
AU42930/12/22EnteredUnavailableNon-work day
AU43730/12/22EnteredUnavailableNon-work day
DU53730/12/22EnteredUnavailableNon-work day
AU4372/11/22NoAvailableAvailable
DU5372/11/22NoAvailableAvailable
AU4374/11/22NoAvailableAvailable
DU5374/11/22NoAvailableAvailable
AU2196/11/22NoAvailableAvailable
AU2216/11/22NoAvailableAvailable
AU4296/11/22NoAvailableAvailable
AU4376/11/22NoAvailableAvailable
DU5376/11/22NoAvailableAvailable
AU4378/11/22NoAvailableAvailable
DU5378/11/22NoAvailableAvailable
AU43710/11/22NoAvailableAvailable
DU53710/11/22NoAvailableAvailable
AU21912/11/22NoAvailableAvailable
AU22112/11/22NoAvailableAvailable
AU42912/11/22NoAvailableAvailable
AU43712/11/22NoAvailableAvailable
DU53712/11/22NoAvailableAvailable
AU43714/11/22NoAvailableAvailable
AU43716/11/22NoAvailableAvailable
AU43718/11/22NoAvailableAvailable
AU21920/11/22NoAvailableAvailable
AU22120/11/22NoAvailableAvailable
AU42920/11/22NoAvailableAvailable
AU43720/11/22NoAvailableAvailable
DU53720/11/22NoAvailableAvailable
AU43722/11/22NoAvailableAvailable
AU43724/11/22NoAvailableAvailable
AU21926/11/22NoAvailableAvailable
AU22126/11/22NoAvailableAvailable
AU42926/11/22NoAvailableAvailable
AU43726/11/22NoAvailableAvailable
DU53726/11/22NoAvailableAvailable
AU43728/11/22NoAvailableAvailable
AU43730/11/22NoAvailableAvailable
AU4372/12/22NoAvailableAvailable
AU2194/12/22NoAvailableAvailable
AU2214/12/22NoAvailableAvailable
AU4294/12/22NoAvailableAvailable
AU4374/12/22NoAvailableAvailable
DU5374/12/22NoAvailableAvailable
AU4376/12/22NoAvailableAvailable
AU4378/12/22NoAvailableAvailable
AU21910/12/22NoAvailableAvailable
AU22110/12/22NoAvailableAvailable
AU42910/12/22NoAvailableAvailable
AU43710/12/22NoAvailableAvailable
DU53710/12/22NoAvailableAvailable
AU43712/12/22NoAvailableAvailable
AU43714/12/22NoAvailableAvailable
AU43716/12/22NoAvailableAvailable
AU21918/12/22NoAvailableAvailable
AU22118/12/22NoAvailableAvailable
AU42918/12/22NoAvailableAvailable
AU43718/12/22NoAvailableAvailable
DU53718/12/22NoAvailableAvailable
AU43720/12/22NoAvailableAvailable
AU43722/12/22NoAvailableAvailable
AU21924/12/22NoAvailableAvailable
AU22124/12/22NoAvailableAvailable
AU42924/12/22NoAvailableAvailable
AU43724/12/22NoAvailableAvailable
DU53724/12/22NoAvailableAvailable
AU43728/12/22NoAvailableAvailable
AU43730/12/22NoAvailableAvailable
v-jayw-msft
Community Support
Community Support

Hi @om3ga ,

 

Please share some sample data and expceted result.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.