Calculate the sales average by hour

[caiodantas]

Hi! 

I've been struggling to figure out how to calculate the information bellow. I would like to have an average of sales amount in an hour per distinct date. 

Sale Datetime		Sale $		Sale Hour
08/03/2023 09:19		91		8
09/03/2023 09:23		95		8
09/03/2023 09:23		200		8
09/03/2023 09:28		180		8
08/03/2023 09:32		25		9
09/03/2023 09:07		108		9
09/03/2023 09:16		290		9
09/03/2023 09:31		107		9
09/03/2023 09:59		209		9
08/03/2023 09:49		175		9
08/03/2023 09:51		180		9
09/03/2023 09:45		263		9
09/03/2023 09:49		256		9
09/03/2023 09:36		44		9
09/03/2023 09:41		121		9
09/03/2023 09:43		45		9

In the data above, there are 4 sales at hour 8 (for my purpose, sale hour is 30 min before), three on March 9th and one on March 8th. When a do a simple measure to get an average per hour, the result is: 91+95+200+180 / 4 = 141.5 . However, since I need the average on distinct days, I was expecting 91 + 95 + 200 + 180 / 2, as there are only 2 different days.

May someone help me?

[mrcss23 · Accepted solution]

Hi. See if it helps

First in power query extract only date 

Then you can create a measure in DAX

Measure = sum(Tabela1[Sale $])/DISTINCTCOUNT(Tabela1[Date])

Create visual table with

* Sale hour (dont summarize)

* Measure

[caiodantas]

Thanks @mrcss23 !
Worked like magic, did not know I just get date only.

Saved my life!

[caiodantas]

Already tried my best, but still could not find a solution