Forum Discussion
Helper ICalculating average over linked tables
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:
Helper IThank 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.
Solution SageCan 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
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:
v-piga-msftResident Rockstar
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