Help - Issue with averages, dividing by select number of days, not total.

[cbickle]

Hi all, 

I have been wrapping my head around this issue for a while - and it's quite hard to explain. 

I'm self taught in Power Bi, so apologies if I don't know the right terms. 

I have a column chart for number of calls on average, against time of day (hour). 

This works fine, but what I want to do is work out on an average day, how many calls are recived each hour. 

I used the divide measure, and did total calls/distinct count of the date (to get total number of days recorded) - which I then plotted on the chart - time vs average per day (as below) 

However, this is wrong. 

The total calls at 8am are 28 and the total days are 267 - so it should around 0.1. 

What it seems to be doing is 28 (total calls at 8am) divided by 25 (total number of days, in which there was an 8am call). 

What I need is 25 divided by 267 (full total days) - I know I can manually type this, but then the date slicer won't work, and it will be out of date tomorrow. 

Any ideas would be much appreciated, as have been stuck on this for hours. 

For reference, below is the measure for the data - with "average time" just being the way all calls are broken into each time slot

[cbickle]

Thank you for this - I have jsut tried, and seems to be the same issue.

I have pasted below as much as I can - the graphs and measures.

The right hand graph is all calls vs time of day - the left is average number of calls per hour vs time of day. The shape should be identical, but the left hand hand graph would give how many we can expect each day. 

The new measure is still doing the same thing and averaging over 1 a day, when that is not the case. 

Below is the average time measue - 

Note: the "Time" column returns 8am, 9am... etc

The "Taken By" is just used to count how many calls have been taken in total. 

Thank you for your help.

[DataInsights]

@cbickle,

You could try using ALLSELECTED to get all dates per the date slicer. I would need to see example data, table relationships, and the DAX for [Average Time] to be sure.

Average Time Per Day =
DIVIDE (
    [Average Time],
    CALCULATE ( DISTINCTCOUNT ( Table2[Date] ), ALLSELECTED ( Table2[Date] ) )
)

[cbickle]

Being new to this, how do I share " I would need to see example data, table relationships, and the DAX for [Average Time] to be sure."

[DataInsights]

@cbickle,

You can provide a link to a sanitized pbix (OneDrive, etc.), along with a screenshot of the expected result.