Need to normalize average hours taking into account holidays

[dcormiernj]

I need help displaying avg hours vs avg hours normalized for holidays.  I have this working for week but doesnt work drilling up and down to month and year.  I feel like I am doing this in a very inefficient way.  Any suggestions?  thank you

I tried to attach below the details i think others would need to understand what i am asking.

I have a measure that calculates average weekly hours normalized for holidays:

Avg Wkly BillHrs Hol Adjustment = 'Measure Financials'[Avg Wkly Bill Hours]*calculate(5/(5-[Holiday count by WEDate]))

this works perfectly when i am displaying week end date with avg hours and avg hours normalized. pasted sample below

Visual sample:

The problem is when i want to drill up to month and then year. the formula i am using is based on holidays by week.

Is there a better way to calculate this so it can drill up and down through year, month and week?

My main data table contains: Emp name, week end date, billable hours as well as other columns. the formulas and other tables im using are below

i can easily get average billable hours by simply doing AVG(billable hours). and the weekly holiday normalization works great.

Avg Wkly BillHrs Hol Adjustment = 'Measure Financials'[Avg Wkly Bill Hours]*calculate(5/(5-[Holiday count by WEDate]))

Holiday count by WEDate = sumx('WE Dates Holiday count','WE Dates Holiday count'[Hol Count])

WE Dates Holiday count table

[dcormiernj]

No im not trying to calculate business hours.  we have avg weekly billable hours which in most cases is a subset of business hours.  but i dont care if it is business i just care if it is billable.  we have total business hours from which i easily calulate the weekly average.  then if that week had one holiday then the normalized avg weekly hours would be calculated as   Avg weekly Bill hours * 5/4  <--  5 days a week divided by non holiday days.  if 2 holidays then mulitple by 5/3.  examples are

weekly avg = 20,  1 holiday in the week,  avg normalized for holidays = 20*5/4 = 25

[amitchandak · Accepted solution]

@dcormiernj , Are you trying to calculate business hours or trying to allocate those?

if try to calculate

https://exceleratorbi.com.au/calculating-business-hours-using-dax/