Forum Discussion
Helper IPer Day / Per Hour / Per Quarter Hour Measures
Hello, I am having trouble coming up with logic to create measures that contain distinct counts per hour and per quarter hour.
I had success creating it per day with the following measures:
M_Count Days =
COUNT(D_Date[Date])M_Transaction Count =
SUM(F_TellerTransactions[Transaction Count])M_Transaction Count / Day =
[M_Transaction Count] / [M_Count Days]
Here are the dimension tables for date and time
Where the Time Table looks like this:
In the end, I want to be able to have measures for 'M_Transaction Count / Hour' and 'M_Transaction Count / Qtr Hr'.
I'd like it to be dynamic so that I can look at it and drill into it by a combination of year, month, day, day of week, weekend/weekday, hour, quarter hour, etc, and for these measures to calculate consistently. Normal COUNT/DISTNCTCOUNT don't do the trick because of how the data is set up.
Thanks in advance for the help!
10 Replies
- lbendlin
Super User
You can either add the hour and quarter hour granularity into your calendar table , or use a standard calendar table (on date level) and then add the hour and quarter categories via measures.
If you were to use a time and a date table, how are you planning to link these into your fact table?
rlusskyThank you for your response lbendlin.
- In the fact table I have a Date (dd/mm/yyyy) column joined to the D_Date table on Date (dd/mm/yyyy)
- * In the fact table I have a Time (h:m am/pm) column joined to the D_Time table on Time (h:m am/pm)
- The D_Time table then has corresponding Hour and Quarter Hour columns
I do have a Date/Time (dd/mm/yyyy h:m am/pm) column in the fact table if I should use that instead.
- lbendlin
If you can afford it, add columns to your fact table for your granularity levels (Hour and Quarter Hour). That would be the simplest and fastest solution, but it would require some storage.
Hi rlussky ,
Are you calculating the average of the counts? Because I saw that you divided the sum of Count by the number of days, you want to calculate the average daily count, right?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- rlussky
Hi Anonymous ,
Yes, I want to be able to calculate the average transactions per day, per hour, and per quarter hour at higher levels of the date and time heirarchy.
- lbendlin
As long as your events don't cross the day, hour, or quarter boundaries this should all be doable with a single measure. If you are sitting at a month level there is no mathematical difference between a daily average or a quarter hour average.
If, however , your events _do_ cross these boundaries, then all hell breaks loose and this becomes quite a bit more complex. My proposal further up to add the two columns should still help, though.
