cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Calculate average per hour per day

Hi - I'm new to dax and power bi, love it but so much still to learn!

I'm working with fulfillment data and I'd like to calculate how many shipments we're averaging per hour per day. It's a very large dataset and packing slips are unique identifier and I've included a small sample below. I am having troubling writing a formula to calculate the average number of packing slips per hour per day.

Would someone help with a hint to get started? Many thanks for advice!

1 ACCEPTED SOLUTION
Community Support

Hi,

I think it is easily to reach your reqiurement.

For test, i make a small change to your sample data:

Please try to create this calculated column:

``Day&Hour = 'Table'[Created date]&"-"&HOUR('Table'[Created time])``

Then create this column to show the Packing slip number:

``Packing slip Number = VALUE(RIGHT('Table'[Packing slip],LEN('Table'[Packing slip])-4))``

Choose above two columns as a table visual, the result shows the average of Packing slip number per day per hour:

Here is my test pbix file:

Hope this helps.

Best Regards,

Giotto Zhi

6 REPLIES 6
Community Support

Hi,

I think it is easily to reach your reqiurement.

For test, i make a small change to your sample data:

Please try to create this calculated column:

``Day&Hour = 'Table'[Created date]&"-"&HOUR('Table'[Created time])``

Then create this column to show the Packing slip number:

``Packing slip Number = VALUE(RIGHT('Table'[Packing slip],LEN('Table'[Packing slip])-4))``

Choose above two columns as a table visual, the result shows the average of Packing slip number per day per hour:

Here is my test pbix file:

Hope this helps.

Best Regards,

Giotto Zhi

Helper II

Super User

Hi,

Share data in a form that can be pasted in an Excel file and on that sample data, show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Thanks for your message, I've pasted the data sample

 Invoice account Sales order Packing slip Version Ship date Mode of delivery Created date Created time 690566419 61467013 PKS-00442659 PKS-00442659.1 1/1/2019 STANDARD 1/1/2019 12:54:37 AM 690745138 61506593 PKS-00442660 PKS-00442660.1 1/1/2019 STANDARD 1/1/2019 12:56:40 AM 685864670 61506650 PKS-00442661 PKS-00442661.1 1/1/2019 STANDARD 1/1/2019 12:56:49 AM 700404151 61506706 PKS-00442662 PKS-00442662.1 1/1/2019 STANDARD 1/1/2019 12:57:02 AM 685543209 61506750 PKS-00442663 PKS-00442663.1 1/1/2019 OVERNIGHT 1/1/2019 12:57:13 AM 700448507 61506760 PKS-00442664 PKS-00442664.1 1/1/2019 STANDARD 1/1/2019 12:57:19 AM 700448539 61506807 PKS-00442665 PKS-00442665.1 1/1/2019 STANDARD 1/1/2019 12:57:27 AM 700448564 61506836 PKS-00442666 PKS-00442666.1 1/1/2019 2 DAY 1/1/2019 12:57:32 AM
Super User

Well, you could create an Hour column, HOUR([Created time]) and then do something like this:

Measure =

VAR __Table =

GROUPBY(

'Table',

[Hour],

"__Count",COUNTX(CURRENTGROUP(),[Invoice account])

)

RETURN

AVERAGEX(__Table,[__Count])

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Super User

As requested in my previous message, for the data that you have shared, show the exact expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.