Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |