Hourly average sales

[Thimios]

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:

      

[talespin]

hi @Thimios 

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

[Thimios]

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

https://we.tl/t-xVpUalv49d

[Thimios]

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

Will try to provide a similar one, hopefully soon.

[Thimios]

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

Pls download this https://we.tl/t-nGsshiJaP9 

[lbendlin · Accepted solution]

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

[Thimios]

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])

[lbendlin]

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

[Thimios]

Thanks for asking @lbendlin .

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

• 'Time Table' is a support table
• SALES = SUM ('Sales'[VALUE])

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?