Forum Discussion

rlussky's avatar
rlussky
Helper I
5 years ago
Solved

Average Transactions Per Hour Measure

Hello,

 

I have a data set that has the following dimensions

* Year, Month, Day, Hour

* Region, Branch, Employee, Cashbox

and a fact table with rows per transaction that has a column [Transaction Count] = 1

 

I am trying to set up a measure that will calculate average transactions per hour per active employee (an employee that has at least one transaction in that hour on that day). This needs to drill down appropriately. I figured I'd have a measure for SUM([Transaction Count]) and find a count of tellers with transaction each hour and just do simple division, but I am having problems with that second part. Do I need a summary table? Could someone help point me in the right direction?

Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi rlussky,

     

    You need create a summary table grouped by each employee per hour, and use Averagex function, just like:

    AVERAGEX(
    filter_table,
    [[Transaction Count]
)

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

3 Replies

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi rlussky,

     

    You need create a summary table grouped by each employee per hour, and use Averagex function, just like:

    AVERAGEX(
    filter_table,
    [[Transaction Count]
)

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

    • rlussky's avatar
      rlussky
      Helper I
      5 years ago

      Thanks v-xulin-mstf. The summary table is definitely the diretion I needed to go. I've been away from PBI for almost a year so I'm a bit rusty!

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi rlussky,

     

    Is your issue solved?

    If the issue has been solved, please adopt the solution to help others.

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!