Average number of rows by hour based on total number of days

[Anonymous]

Dear all,

Let's look at this sample data:

ID	DATE	TIME
A	2019-11-01	13:00
B	2019-11-01	13:00
B	2019-11-01	14:00
C	2019-11-01	14:00
A	2019-11-02	13:00
B	2019-11-02	13:00
B	2019-11-02	16:00

We can see that wee have this total number of occurrences per each hour per day:

2019-11-01:

13:00 - 2 occurrences

14:00 - 2 occurrences

2019-11-02:

13:00 - 2 occurrences

16:00 - 1 occurence

We do have a total number of 2 days, right?

I would like to have the sum of of occurences by hour per day, divided by the total number of days.

So that's the result that I would like to have:

13:00 - Average: 2 <--- (2+2)/2

14:00 - Average: 1 <--- 2/2

16:00 - Average: 0.5 <--- 1/2

Everything that I've tried I came up with the following result:

13:00 - Average: 2 <--- (2+2)/2

14:00 - Average: 2 <--- 2/1

16:00 - Average: 1 <--- 1/1

It happens because it divide the sum of occurences by the number of days that contains that hours, but I would like to, in fact, divide it by the total number of days that exists in the table.

Does someone have an idea how could I solve it?

It seems to me that I should first come up with a Measure that count the number of days but is independent of the time.

Note: The data is a bit more complicated, the time has hours, minutes, seconds and I have a Time Dimension Table to summarize it by complete hours.

Thanks in advance!

[Anonymous · Accepted solution]

Hey guys, appreciate your help!

Unfortunately I couldn't get the desired result with your tips.

However, another person helped me and I could figure out how to solve it.

This is the solution:

formula1
Occurrences = COUNTA( DataTable[id] )

formula2 
CountDays = CALCULATE ( DISTINCTCOUNT ( DataTable[date] ), ALLSELECTED ( 'DataTable' ) )

formula3
Avg_Occurrences = DIVIDE( [Occurences] , [CountDays] )

 Now I can use the Avg_Occurrences measure in a matrix, using a Dimension Table column for the hours.

One more time, thank you for your help!

[Icey]

Hi @Anonymous ,

Glad to hear that. Please accept your reply above as a solution so that people who may have the same question can get the solution directly.

Best Regards,

Icey

[Icey]

Hi @Anonymous ,

You can create your measure like so:

Avg =
VAR CountOfTime =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        ALLEXCEPT ( 'Table', 'Table'[DATE], 'Table'[TIME] )
    )
VAR DistinctCountOfDate =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[DATE] ), ALL ( 'Table' ) )
RETURN
    DIVIDE ( CountOfTime, DistinctCountOfDate )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

You need to count and Avg. Try this

formula 1
Count = SUM('Table'[Hours])

formula 2
Sum Amount = 
AverageX(
    VALUES(Table[Date])  // count till Day level
    , [Count]
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hey @amitchandak , thanks for replying.

Couldn't get my desired result with your tip.

Let me provide you a more realistic sample data.

DataTable:

ID;DATE;HOUR
715;2019-10-19;15:47:37
181;2019-10-19;15:56:11
349;2019-10-19;15:57:25
6ec;2019-10-19;15:58:16
57e;2019-10-19;16:02:35
860;2019-10-19;16:03:42
7a5;2019-10-19;16:03:52
978;2019-10-19;16:05:19
da0;2019-10-20;11:00:45
c2d;2019-10-20;23:04:53
355;2019-10-20;23:04:53
4f5;2019-10-20;23:05:10
396;2019-10-21;14:42:24
5f7;2019-10-21;14:43:37
93a;2019-10-21;14:55:36
a44;2019-10-21;14:59:21
264;2019-10-21;15:05:20
f48;2019-10-21;15:07:01

And a summarized Dimension Table with the values present in DataTable:

DimHourTable:

COMPLETEHOUR;HOUR24
15:47:37;15
15:56:11;15
15:57:25;15
15:58:16;15
16:02:35;16
16:03:42;16
16:03:52;16
16:05:19;16
11:00:45;11
23:04:53;23
23:04:53;23
23:05:10;23
14:42:24;14
14:43:37;14
14:55:36;14
14:59:21;14
15:05:20;15
15:07:01;15

Note: Relationship with Both Directions filter between DataTable[HOUR] and DimHourTable[COMPLETEHOUR]

I'm now doing this:

formula1
Occurrences = COUNTA( DataTable[id] )

formula2 
CountDays = DISTINCTCOUNT( DataTable[date] )

formula3
Avg_Occurrences = DIVIDE( [Occurences] , [CountDays] )

Then I'm putting in a matrix the following

Rows: DimHourTable[HOUR24]

Values: Avg_Occurrences

With that Sample Data, this is the average I'm getting.

11 -> 1
14 -> 4
15 -> 3
16 -> 4
23 -> 3

But, in reality, I would like to have this:

11 -> 0.33
14 -> 1.33
15 -> 2
16 -> 1.33
23 -> 1