I'm having trouble with the DAX formula to generate the average of the counts over a period of time.
As you can see by this screenshot, the AVERAGE comes out to be the same as the COUNT.
This is the formula I'm using:
but, I've tried multiple varations of it:
DIVIDE ( [Total Tickets], DISTINCTCOUNT ( Calendar[WeekID] ) ),
ALL ( calendar ),
>= MAX ( calendar[weekID] ) - 12
&& calendar[WeekID] <= MAX ( calendar[WeekID] )
They all just produce the COUNT.
Additional info: I took the DATE/TIME field, and split out the Date and Time, and then I rounded the TIME, so that I could COUNT how many tickets came in during that time, so my data looks like this:
Anyone have an idea of why I'm not able to generate the actual AVERAGE?
Thanks in advance for your help!
Solved! Go to Solution.
OK, so the top visual is correct then? And in the bottom visual, you want to see the AVERAGE per day for each hour for the selected date range in the slicer above? Are those all correct statements?
If those are all correct statements, you could do a COUNTROWS of your calendar table (if your slicer is based upon that) and use that as your denominator.
Take a look at these two Quick Measures as I think you want something like them.
Thanks for the reply, and there's a lot of good info over there, but not quite what I need, I don't think. It could be that I'm not understanding the functions, correctly, though...
I may not be understanding exactly what you are trying to do, but what AVERAGE are you trying to come up with? Because the AVERAGE of a single day or hour is going to be the same as COUNT for that day or hour obviously. So, are you trying to AVERAGE between multiple days or some kind of cumulative thing?
Yes, sir. It's the average of all tickets received over the last 12 Mondays during the 7:00 AM hour. (We may have received 137 tickets total during this time frame. What was the average number of tickets received each Monday during that hour?).
The dashboard can be filtered, by my Director, to then select the 8am hour, and the last 5 Mondays OR the 10am hour, and the last 20 Thursdays, etc... (she actually selects the start and end dates, plus the day of the week she's looking at).
Ok, I think I figured out part of my problem... I wasn't using an expression. (Your comment, plus something a co-worker mentioned, made it click for me).
This is now the new formula I'm testing (which is now giving me a different number, but it's not the average of the values selected).
Average per OwnedByTeamAssignedTimeHour =
Now to try and figure out, the right expression.
If you still have the problem on it, Could you please share your sample pbix or some data sample and expected output.
You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Unfortunately, my company blocks access to all 'cloud' locations due to the nature of our data (HIPAA). With that in mind, I cannot share any of our data, either, due to some of the HIPAA info exists within our ticketing system. It would take me too long to re-create fake data.
The expected output is the 'average' of the count of rows (not the data within the row) over a specific period of time.
Sample data doesn't need to recreate things exactly, just get the necessary columns to recreate things. For example, is the essentials of your data like:
The big issue I see here is that it seems like you are using specific dates in your axis. If you want to average Mondays, then you need to use the weekday in your axis and not specific dates. So, if you had the above, you could create a column like:
Weekday = WEEKDAY([Date])
Then use that in your axis and then your formula may work as is.
True, I posted a pic of the date info above, so I thought that would help explain it, but I know it's the same thing as having an actual data sample.
I do have a CALENDAR table, and am using WEEKDAY for the axis, my director just wanted to see the dates (of the days she was looking at), so I had to finagle it to show it to me, too.
The Orange arrow points to where I'm using WEEKDAY.
The red arrows are pointing to what I expect the data to look like... I hard coded the formula to count the rows, and divide by seven (the number of days I'm filtering to find).
I'm trying a different approach of now trying to find a way to remove the 'hard-coded' seven, and Power BI to use the filter here.