Formulas based on working days in a week

Hi v-easonf-msft ,

Long story short, what I want is to find a way in which I can divide/split my calculated measures based on the number of working days in a week.

To take a random example, let's say I am a company which sold the X amount in Week Y, I want to be able to see the X split by every working day which is part of week Y (For example, Monday, Tuesday, Wednesday, Thursday, Friday).  The simplest solution would be to have X / 5 because most of the weeks have 5 working days, but what happens when we have working weeks of 4 or of 6 days ? The formula needs to change dynamically so I can have an accurate trend when I look in the past.

My solution was to create a separate lookup file as you can see in my initial post, where I assigned to every working week day in a week the value of 1. so for a 5 days working week, every working day has a value of 1 (Working weekdays =1 * 5 = 5). For a week with 4 working days, Working weekdays = 1 * 4 = 4.

This solution was wrong for 2 big reasons (also check my initial post for more information):

1. If for one or more than one day in that week I don't have any sales value, the total will divide by the reamining days which is not corect for me. (So if I don't have sales on Monday, the Sales will divide by 4 and not by 5, even if the week had 5 working days).

2. I can't see the values INDIVIDUALLY split by each working day, see the pic on my initial post. Because my solution was to assign 1 to every working day, the sales amount for each individual working day is the same as the total, since it is divided by 1 and not by the sum (5 etc.)

Hope I was more clear in my explanations now.

Thank you,