cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelK Frequent Visitor
Frequent Visitor

Aggregates of counts over time

Hi

 

I am still quite new to power BI and working as a BI analyst so please bare with me 🙂

 

The data that I am trying to work with is numeric.

 

Each item has what is called a work item ID (used to uniquely identify each work item) and a corresponding event time.

(there can be multiple line items with the same work item ID as the instruction moves through different queues).

The work item ID is a string of intergers ie: 3707193.

 

I am trying to calculate the average amount of instructions being created per hour.

It is easy to measure the totals as I can just plot my create date against the distinct count of work item IDs.

 

BUT when I try select average/median/etc it tries to give me the average my summed work item IDs.

For example, I get 3 instructions on Monday (week 1), 5 on Monday (week 2) and 12 on Monday (week 3).

My average should be 6.67 recurring.

But when I select average on power BI I get a massive number because it is summing the actual numbers given to each work item ID.

 

I need to find a way where I can aggregate counts only....

I've tried creating columns and measures of distinct (work item id) etc but all of my results are returning incorrect figures.

 

 

My ultimate goal is to show averages and medians of new instructions created by the hour as to measure head count needed to process those instructions.

 

Any ideas/advice will be greatly appreciated!

 

Thanks 🙂

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Aggregates of counts over time

Hi @MichaelK,

 

>>So I would like to calculate how the average moves through out the day

 

You can add a slicer to get the average moves through out the day.

 

Slicer:

Capture.PNG

 

Line chart:

 

Capture2.PNG

 

Use slicer to filter the result of specify hour daily movement.

 

Capture3.PNGCapture4.PNG

 

 

>>And then how that average changes from 11am to 12pm, 12pm - 1pm etc.

You can use below measure to see the hourly movement:

 

Average of hour:

Avg Hour = AVERAGEX(FILTER(all(Sheet1), Sheet1[Hour]=max(Sheet1[Hour])),[Hourly Count])

 

Capture5.PNG

 

Notice: the hour means the hour:00~hour:59:59.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

5 REPLIES 5
Highlighted
Community Support Team
Community Support Team

Re: Aggregates of counts over time

Hi @MichaelK,

 

According to your description, you want to get the average and middle of weekly count,right?

 

You can refer to below sample:

 

Source table:

Capture.PNG

 

Measures:

 

Count of same date and hour:

Hourly Count = COUNTAX(FILTER(ALL(Sheet1), FORMAT([Date],"MM/dd/yyyy HH")=FORMAT(MAX([Date]),"MM/dd/yyyy HH")),[Work Item ID])

 

Count of same week and hour:

Weekly Count = COUNTAX(FILTER(ALL(Sheet1),WEEKNUM(Sheet1[Date])=WEEKNUM(MAX([Date]))&&HOUR([Date])=HOUR(MAX([Date]))),[Work Item ID])

 

Avg Week = AVERAGEX(ALL(Sheet1),[Weekly Count])

 

Med Week = MEDIANX(ALL(Sheet1),[Weekly Count])

 

Create a table visual to show the result:

Capture2.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
MichaelK Frequent Visitor
Frequent Visitor

Re: Aggregates of counts over time

Thanks a lot

 

Going to give it a try and will post feedback.

MichaelK Frequent Visitor
Frequent Visitor

Re: Aggregates of counts over time

Power BI does not let me aggregate the measure.

 

I create a new measure named hourly count and when I try create a measure to aggregate it, the measure does not appear in the predictive text. When the measure is typed in manually it gives me an error.

 

Also I'm not sure if what you provided is what I am trying to go for.

 

What I actually want is a moving average and moving median.

 

To further explain our data, I only want to be working with WORK_ITEM_ID and CREATE_DATE at the moment.

 

I have extracted the hours of the day from CREATE_DATTE.

I have then plotted the hours of the day against distinct count of WORK_ITEM_ID.

This has given me the total amount of WORK_ITEM_ID received per hour.

Bevcause our data goes back about a year, for example, between 10am and 11am it will show 10 000 instructions (all instructions received since the beginning of time of our data set between 10am and 11am).

I'd like to see the average amount of instructions received between 10am and 11am.

And then how that average changes from 11am to 12pm, 12pm - 1pm etc.

So I would like to calculate how the average moves through out the day

Community Support Team
Community Support Team

Re: Aggregates of counts over time

Hi @MichaelK,

 

>>So I would like to calculate how the average moves through out the day

 

You can add a slicer to get the average moves through out the day.

 

Slicer:

Capture.PNG

 

Line chart:

 

Capture2.PNG

 

Use slicer to filter the result of specify hour daily movement.

 

Capture3.PNGCapture4.PNG

 

 

>>And then how that average changes from 11am to 12pm, 12pm - 1pm etc.

You can use below measure to see the hourly movement:

 

Average of hour:

Avg Hour = AVERAGEX(FILTER(all(Sheet1), Sheet1[Hour]=max(Sheet1[Hour])),[Hourly Count])

 

Capture5.PNG

 

Notice: the hour means the hour:00~hour:59:59.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

MichaelK Frequent Visitor
Frequent Visitor

Re: Aggregates of counts over time

Thank you!

 

Will give it a shot as soon as I get tthe time.

 

Thanks so much for the response!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors