Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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)
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 |
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.
User | Count |
---|---|
98 | |
91 | |
84 | |
72 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |