Public holiday differ by states - working days and daily average

[corange]

Hi everyone, 

I am after some help as I am unsure how to go about it. I want to be able to calculate daily average with the ability to have the metric update when I select a specific state. I have four tables : 

1) States List 

2) Fact Table with Sales / Profit - this is a list of individual orders per day

3) Calendar Table 

4) Public Holiday table with a column for each states of Australia as they differ from one state to another: 

The relationships between the tables are as follows: 

In the Calendar table I have created a calculated column for each state using the following formula to calculate the number of working days specific to a state:   WA_WorkingDays = IF(vw_Calendar_BI[DayName]="Saturday" || vw_Calendar_BI[DayName]="Sunday",0,1 - IF(RELATED(vw_PublicHolidayMatrix[WA])=1,1))

Then, in my fact table, I have tried to create another calculated column using the following: 

 = if(RELATED(States[State])="NSW",calculate(sum(Dates[NSW_WorkingDay]), if(RELATED(States[State])="NT", calculate(sum(Dates[NT_WorkingDay]), if(RELATED(States[State])="WA",calculate(sum(Dates[WA_WorkingDay]) The issue is that I have many sales entries for any single day whihc mean that the later formula will duplicate the number of working days giving me an insane number to calculate my daily average. 

How could I use the related function in the fact table to only calculate the number of working days only once so I can calculate my daily average? Or maybe I could do something else? 

Thanks in advance for your help. 

[Rody]

Hi Corange,

any chance you found a solution to this as I am struggling with this too?

Thanks mate

[lbendlin]

You will want to unpivot your holidays table so that it lists day and state, rather than having states in columns.

After that change your relationship between the calendar and holidays table as it is no longer 1:1

After that your formulas will be much simpler.

[corange]

Ok, but what would the formula look like then? Would it be in the fact table as a calculated column or else? 

I am sorry, but your answer remains really vague to me. I am not sure how I could implement.