Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Trying to Calculate the Average Number of Actions Per Minute, Per User

Hello There, 

Fairly new to Power BI, so I appreciate all the help I can get.

What I'm trying to do is calculate the number of Actions Per Minute, Per User. 

I'm honestly a bit of a loss as to where to begin explaining, but maybe the screenshow below will help:

911Data_0-1596490664831.png


As you can see, with the current filter/slicer, between 04/1 to 08/03, there have been a total of 1.85M actions by each and every user combined.

However, when using the following formula:

AVERAGEX(
    KEEPFILTERS(VALUES('incilog'[timestamp - minute])),
    CALCULATE(COUNTA('incilog'[descript]))
)

I end up getting an average that continues to significantly increase, as the number of dates included in the graph increases. I was under the assumption that the average should stay roughly the same throughout the dates, as the number of agents hasn't changed significantly, and output by agent shouldn't change that drastically either.

Here is an example of a single days action count:
 

You'll notice that the number has changed significantlytest.jpg


Any ideas as to what I could be doing wrong?

I'm getting the number of actions by doing a count of the number of transaction types (descript) that appear in a particular column.
And I'm getting the number of minutes, by doing a distinct count of the number of minutes in a day (timestamp-minute). 

Please let me know if you need more info to address this.
Thank you for your time and assistance.

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Your average is increasing because all the counts for the days are aggregated in your minutes.  More days = more actions that occurred in that minute.  To solve it you need to add date and hour granularity as follows

 

AvgActionsPerMinutes = 

AVERAGEX(
    Summarize('incilog', 'incilog'[Date], 'incilog'[Hour], 'incilog'[timestamp - minute]),
    CALCULATE(COUNTA('incilog'[descript]))
)
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Could you pls upload your .pbix file to onedrive business and share the link with us?

 

 
 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
mahoneypat
Employee
Employee

Your average is increasing because all the counts for the days are aggregated in your minutes.  More days = more actions that occurred in that minute.  To solve it you need to add date and hour granularity as follows

 

AvgActionsPerMinutes = 

AVERAGEX(
    Summarize('incilog', 'incilog'[Date], 'incilog'[Hour], 'incilog'[timestamp - minute]),
    CALCULATE(COUNTA('incilog'[descript]))
)
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


parry2k
Super User
Super User

@Anonymous very hard to say what is going on? Remove Keepfilter in your average measure, not sure if that is required. if it doesn't work, paste some sample data and we can look into it.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.