Displaying Weekly Avg sales in Hour Bucketing

[Anonymous]

Hi Experts,

My requirements is show Avg daily sales weekly and avg daily sales todate in hourly bucket(11am to 1 pm and 1 pm to 3 pm).

something like below

[amitchandak]

@Anonymous ,

Can you provide sample data sample output

[Anonymous]

@amitchandak  sir,

Here is my sample data. i also need to calculate avg sales todate daily

Avg Sales Per Day =

AVERAGEX (

VALUES('Calendar'[Date]),[Sales Rev])

Hour Bucket =

SWITCH (

TRUE (),

HOUR ( Timestamp[timestampUTC] )

+ MINUTE ( Timestamp[timestampUTC] ) / 60 >= 11

&& HOUR ( Timestamp[timestampUTC] )

+ MINUTE ( Timestamp[timestampUTC] ) / 60 < 13, "11:00 a.m. - 1:00 p.m.",

HOUR ( Timestamp[timestampUTC] )

+ MINUTE ( Timestamp[timestampUTC] ) / 60 >= 13

&& HOUR ( Timestamp[timestampUTC] )

+ MINUTE ( Timestamp[timestampUTC] ) / 60 < 15, "1:00 p.m. - 3:00 p.m."

)

[v-xuding-msft]

Hi @Anonymous ,

Have you tried the formula which amitchandak suggests? If yes, please mark the answer as a solution. If you still have problems, please share the image again. It is not working.

Best Regards,

Xue Ding

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

[Anonymous]

@v-xuding-msft  yes it is working. thanks

[amitchandak · Accepted solution]

Try like

calculate(AVERAGEX (summarize(calculatetable('Calendar',datesytd('Calendar'[Date]))
    ,'Calendar'[Date],"_sales",[Sales Rev]),[_sales]))