Calculate average per hour per day

[abujouz86]

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!

[v-gizhi-msft · Accepted solution]

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:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[abujouz86]

Thank you Giotto, so helpful! 

[Ashish_Mathur]

Hi,

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

[abujouz86]

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

[Greg_Deckler]

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

[Ashish_Mathur]

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