topic Re: Hourly average sales in DAX Commands and Tips

Hourly average sales

Thimios — Mon, 26 Feb 2024 20:36:48 GMT

Hi all,

I have a large sales table (sample below) with sales lines for the last 5 years.

I need to calculate average hourly sales per year so I can have multiple lines in a visual such as the one below:

lbendlin — Tue, 27 Feb 2024 01:17:13 GMT

Sounds good. What have you tried and where are you stuck?

Thimios — Tue, 27 Feb 2024 05:45:45 GMT

Thanks for asking lbendlin .

Tried using Sales Avg Hourly = AVERAGEX( VALUES( 'Time Table'[Hour] ), [SALES] ) where

What I get in line chart, shows Total Sales (the sum of sales) instead of the average.

Here is the above as table:

Could it be that a miss something about hte context?

talespin — Tue, 27 Feb 2024 12:01:33 GMT

Is it possible to share pbix file sample data and with all sensitive data removed.

Thimios — Tue, 27 Feb 2024 12:49:23 GMT

Hard one talespin. This is a huge model and requires lot of work...

Will try to provide a similar one, hopefully soon.

Thimios — Tue, 27 Feb 2024 19:35:35 GMT

Here you are. Let me know if you need additional info.

Thimios — Tue, 27 Feb 2024 19:41:43 GMT

Not sure if the last one was the latest version talespin .

lbendlin — Wed, 28 Feb 2024 01:38:22 GMT

You didn't specify if you want this per order or per line item. I assumed the former.

Thimios — Wed, 28 Feb 2024 06:07:24 GMT

Thank you lbendlin, worked perfectly.

I also created a similar measure per day.

Sales Avg Daily =

var a = summarize('SALES', SALES[DATE])

var b = ADDCOLUMNS(a,"sm",CALCULATE(sum(SALES[VALUE])))

return AVERAGEX(b, [sm])