Calculate Average Based on Category Count and Date

[asmita_sharma]

Hi,

I'm trying to build a graph like below :

where only the categories with maximum count (from bottom) or maximum count (from top) are considered while trying to graph it based on the average time taken to finish a task.

As there are like 100's of categories with 100's of counts (as it's a dataset worth this whole year) - it graph's with even if it's got one value (highest or lowest - in my case it will be an outlier if that makes sense). I'm trying to look for an average trend rather than individual result and hence looking for top 5 (shortest time) average/bottom 5 (longest time) average over a period of time (in this instance - every month/ but would also like every month too)

Any help with the dax query would help.

I would prefer not to create an additional summarize table as already have several tables linked in the backend.

I've attached a dummy data too.

[asmita_sharma]

Hi @v-rzhou-msft As you see in your graph, there's one data point for pencil - that's what I do want to ignore and only average out the category which has a count of more than 5 - because when I do put the measure as stated above - it gives me all the results (including outliers - which means anything below than count of 5 as well)

[asmita_sharma]

Dummy Data:

Date	Category	Time (Mins)
20/01/2022	Pen	5
20/01/2022	Paper	10
20/01/2022	Pencil	15
20/01/2022	Pen	20
21/01/2022	Pen	1
22/01/2022	Pen	2
23/01/2022	Paper	0
24/01/2022	Paper	5
25/01/2022	Paper	10
26/01/2022	Paper	4
27/01/2022	Paper	2
28/01/2022	Paper	6
29/01/2022	Paper	7
29/01/2022	Paper	1
29/01/2022	Paper	7
29/01/2022	Paper	45
30/01/2022	Paper	2
31/01/2022	Paper	3
1/02/2022	Paper	25
2/02/2022	Paper	6
3/02/2022	Paper	66
4/02/2022	Paper	88
5/02/2022	Pen	100
6/02/2022	Pen	130
7/02/2022	Pen	150
7/02/2022	Pen	170
5/02/2022	Pen	190
4/02/2022	Pen	220
4/02/2022	Pen	235
7/02/2022	Pen	260
7/02/2022	Pen	200
7/02/2022	Pen	30

[v-rzhou-msft]

Hi @asmita_sharma ,

I suggest you to try this measure.

Measure = 
VAR _ADD = ADDCOLUMNS('Table',"YearMonth",YEAR([Date])*100+MONTH([Date]))
VAR _ADDRANK = ADDCOLUMNS(_ADD,"RANK1",RANKX(FILTER(_ADD,[YearMonth] = EARLIER([YearMonth]) && [Category] = EARLIER([Category])),[Time (Mins)],,DESC,Dense),
"RANK2",RANKX(FILTER(_ADD,[YearMonth] = EARLIER([YearMonth]) && [Category] = EARLIER([Category])),[Time (Mins)],,ASC,Dense))
RETURN
AVERAGEX(FILTER(_ADDRANK,[RANK1]<=5||[RANK2]<=5),[Time (Mins)])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.