Forum Discussion
Calculating average over linked tables
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.
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:
8 Replies
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]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.
Can you share a copy of that matrix with the Total Arrivals and Days on the columns.
