Avg Hrs by Category for last 10 (TOPN) Ticket Number for selected Date range

[itsme]

I have a dataset that contains the number of coding hours by each person for tasks in different categories. I need to find the average coding hours for each category for the last 10 (support) tickets in each category. This also needs to be dynamic with the selected date slicer. Once the avg hrs have been computed for each category, I need to add up the hours each person spent for the given date slicer. Since the dataset has unique Ticket #'s, the last part would be calculated by multiplying the avg hrs for the given category by the row count (row count is always equal to 1). Also, each Ticket # increases, so my TOPN calc uses Ticket # in DESC order (can't use date to order because there are many Ticket #'s each day).

The TOPN calc seems to be working in most of the tests, except when I add the person into the table visual. This is telling me that I need to set TOPN to ignore any filter on Person. In fact, I believe the only filters / filter context it should allow are Date and Category. Below are tables and measures to show my work and examples. Unfortunately, I cannot provide the file as it contains confidential info.

There are 15 tickets in this example table (Ticket # already in DESC order). The real data set has more columns too, like the support person, date, etc. There are also many more categories. We need to calculate the avg hrs coded for the 10 most recent tickets for each category. So, that calculation would only use the first 10 rows of this table.

Ticket #	Category	Hrs Coded
1021762	Workstation Software	0.22
1021742	Workstation Software	0.2
1021730	Workstation Software	1
1021726	Workstation Software	0.28
1021697	Workstation Software	1.32
1021660	Workstation Software	0.66
1021650	Workstation Software	0.17
1021635	Workstation Software	0.27
1021633	Workstation Software	0.48
1021630	Workstation Software	0.27
1021617	Workstation Software	0.2
1021596	Workstation Software	1.37
1021374	Workstation Software	0.27
1021338	Workstation Software	0
1021326	Workstation Software	0.47

Let's say that the table below contains the avg hrs coded for the 10 most recent tickets in each category, given the dates are not filtered (so all dates).

Category	Avg Hrs TOP10
Workstation Software	0.49
Change Perms	0.39
Outlook	0.18
SaaS	1.11

Say Lisa had 4 Workstation Software tickets, 2 Outlook tickets, and 3 SaaS tickets. Bart had 2 SaaS tickets and 5 Change Perms tickets. I would multipley the Avg Hrs TOP10 measure by the # of Tickets measure (COUNTROWS( Table ) for the latter). Lisa and Bart would end up with the following number of hours coded:

Lisa: (4 x .49) + (2 x .18) + (3 x 1.11) = 5.65

Bart: (2 x 1.11) + (5 x .39) = 4.17

And of course, the Avg Hrs Top10 measure would change based on the selected date range. Here are the measure I have below:

Ticket Count =

COUNTROWS ( Table )

Avg Hrs TOP10 =

CALCULATE (

AVERAGE ( Table[Hours Coded] ),

TOPN ( 10, VALUES(Table[Ticket_Number]) , Table[Ticket_Number], DESC )

Avg Hrs Coded =

//IF used b/c values and totals weren't showing up correctly.

IF (

[Ticket Count] > 1,

SUMX ( Table, [Avg Hrs TOP10] * [Ticket Count] ),

[Avg Hrs TOP10] * [Ticket Count]

)

The end result needs to be a table or bar chart or any visual with these values by person. Currently, Avg Hrs TOP10 is not ignoring the Person filter context, therefore, it is not multiplying the correct average hours.

Please help! Thank you so much in advance!

[itsme · Accepted solution]

I ended up using a formula very similar to the one @Jos_Woolley provided - so thank you for your contribution and laying out the framework. I ended up using ALL VALUES instead of ALLEXCEPT. 

Avg Hrs Coded =
SUMX (
    'Table',
    CALCULATE (
        [Avg Hrs TOP10],
        ALL ( 'Table' ),
        VALUES ( Table[Category] )
    ) * [Ticket Count]
)

 I also needed to create another version of this measure that was dynamic with the date slicer, so it would pick the 10 most recent tickets within the given date range and calculate the average hours coded for each category. To do this, I added VALUES ( 'Calendar'[Date] ) after the first VALUES function.

Thanks everyone for the assistance! Hope this ends up helping someone else in the future!

[Jos_Woolley]

Hi,

Have you tried:

Avg Hrs Coded :=
SUMX(
    'Table',
    CALCULATE( [Avg Hrs TOP10], ALLEXCEPT( 'Table', 'Table'[Category] ) ) * [Ticket Count]
)

Regards

[itsme]

@Jos_Woolley  Thank you. It looks like I had the right idea but needed to have CALCULATE inside of SUMX and multiply Ticket Count after the CALCULATE function. So thank you! I also added Calendar[Date] inside ALLEXCEPT so that the TOP10 calculation is dynamic.

So far, the measure is working properly on some visuals, like a table that contains the resolver and category. but not others like a bar chart where the axis is the resolver's name and the values are Ticket Count and the measure you just provided. I'm trying to break it apart and try a few things.

I previously experimented with ALLEXCEPT but tried avoided using it, especially after reading an article about it in sqlbi.com. I will continue working on this tonight or tomorrow to figure out what's going on. I'm wondering if I need to be using ALL and VALUES instead of ALLEXCEPT. ALLEXCEPT can be a bit complex behind the scenes and sometimes it's not so clear what exactly it is doing.

[itsme]

I created an example dataset, you can download it here.

Let me know if you have problems getting to it, but I think that should work.

Thank you!

[lbendlin]

What does "Last 10 tickets" mean?  By what, ticket number?  Or by some other field? Please provide sanitized sample data that fully covers your issue. 

[itsme]

Hey @lbendlin I provided a table in my description with Ticket #, Category, and Hrs Coded. Think of it as support tickets. Every time a customer calls support, a new ticket is created and a ticket # is assigned.

What does "Last 10 tickets" mean? This means the 10 most recent tickets. I need to take the 10 most recent tickets for each category and calculate the avg hrs coded.

If you take a look at Avg Hrs Top10 measure above, you'll see it's almost complet and is missing one thing. I just need to figure out how to make this measure ignore filter context on Support Person (Lisa and Bart in my example). For example, if I have Lisa and Bart or any other Support Person in a table visual, I need Avg Hrs Top10 to ignore that Support Person is in the filter context (on each row of the visual) and still calculate avg hrs coded for the 10 most recent tickets in each category.

[lbendlin]

You mentioned 10 most recent tickets by category. However your sample data only shows one category. Please provide sample data that fully covers your issue.