Calculating average over linked tables

[pgolbi]

Hello everyone

Can someone please help with this task. I need to build several measures like:

Average Number of Arrivals per Week Day 

Average Number of Arrivals per Hour

I split data in to 2 dimension tables - Date and Time and linked them to main table Raw Data. Number of Arrivals will be count of rows and ket fields are Arrived and Departure. For experienced developers it should not be hard but I am new and my head is spinning so far without much output. Thank you.

[Seward12533]

Not sure if your going to want a single date-time key and single date-time table in order to avoid any issues if your trying to show both of these values in the same visuals. but try this with your current model. 

Total Arrivals = DISTINCTCOUNT(COUNTROWS(FACT)
DAYS = DATEFIFF(MAX(fact[arrival date key]),MIN(fact[arrival date key],day)
HOURS = DATEFIFF(MAX(fact[arrival hour key]),MIN(fact[arrival hour key],hour)
AVG per Hour = DIVIDE([Total Arrivals],[Hours])
AVG per Day = DIVIDE([Total Arrivals],[Days]

[pgolbi]

Thank you, it works in some cases. Just when it comes to filtering it generates incorrect results. Example is below:

Total average is correct but by week day it is not. I know it is to do with filtering context as shown below. 
Could you please help with this particular example and I try to figure out what needs to be done for similar reports?

Thank you again.

[Seward12533]

Can you share a  copy of  that matrix with the Total Arrivals and Days on the columns.

[pgolbi]

Hope this is what you need, thank you for looking into this. Seems like Total Arrivals per day is fine but not DAYS. 

DAYS = DATEDIFF(MIN('Raw Data'[Arrived]),MAX('Raw Data'[Arrived]),DAY)

For the given report I selected March and April and it calculated 56 as difference between first Monday of March and last Monday of April. I guess instead it should be 9 - number of Mondays within that period. 

[pgolbi · Accepted solution]

I think I found solution to this. 

Days = CALCULATE(COUNTROWS(Date), ALL(Date[Week Day]))

where Date is dimension table. This only works when I filter by month. There should be a way to display correct figures regardless of if month is selected or not. Anyway, it is all about learning report contexts and matching what is required: 

[Seward12533]

Honestly, the original formula should work. Is your date table smaller than your file? Does it have days missing? Or do your rows in your fact table without dates (ie nulls, 0, etc)

Key concept is that each cell in a table is calculated independently of all others. To the total is calculated without the filter context of the rows above vs being a sum.

[pgolbi]

Date table is generated as this Date = CALENDAR(DATE(2015,01,01), DATE(2020,06,30)) from fact table.

Fact table has all [Arrived] populated, no blanks or zeros.  

As suggested I am coming to conclusion that I need to use one Date/Time table as I only need to go down to hour level that should not create too many records. Having Date and Time dimension tables separately makes things more complex.

Toooo much to learn still for me ...  

[v-piga-msft]

Hi @pgolbi,

It's  glad that you have solved your problem. Please accecpt you answer as a solution so that people who may have the same question can get the solution directly.

Best Regards,

Cherry