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 have 2 queries:
1. I want to show Top 5 Workstreams for each Week based on the total count of the Work Items for the particular workstream in a given week.
So in Bar chart I am taking Axis --> "Week" (It is derived Column from CreatedDate Column)
In Legend --> "Workstream"
In Values --> I tried taking Measure ==> "TOP 5 Workstream" = CALCULATE( COUNT('Bugs DevOps'[Work Items]), TOPN(5,ALL('Bugs DevOps'[Workstream]), COUNT('Bugs DevOps'[Work Items]),DESC), VALUES('Bugs DevOps'[Workstream]) )
2. My 2nd query is that, In Values I can Just take --> "Work Items" (Count of Work Items)
and I can add TOPN visual filter on Legend Field (i.e. Workstream), but I am getting top 5 in only latest Week. PFB Snip FYI.
In above snip for Feb Week 8 --> I am not getting Yellow color (with value 15 that is there in above chart). Basically it is giving same TOP 5 Values (based on the latest week) for all the weeks. So is it possible to get Top 5 values (Workstream) for each category (Week) in the chart??
Please help me with the above queries. I also need DAX formula for this.
Solved! Go to Solution.
Hi @lordmukund ,
Try the following formula to create measures:
Conut Work Items = COUNT('Bugs DevOps'[Work Items])
TOP 5 Workstream =
VAR Top5Cat =
CALCULATETABLE (
GENERATE (
VALUES ( 'Bugs DevOps'[Week] ),
TOPN (
5,
CALCULATETABLE ( VALUES ( 'Bugs DevOps'[Workstream] ) ),
[Conut Work Items]
)
),
ALLSELECTED()
)
RETURN
CALCULATE (
[Conut Work Items] * ( NOT ISEMPTY ( 'Bugs DevOps' ) ),
KEEPFILTERS ( Top5Cat )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @lordmukund ,
Try the following formula to create measures:
Conut Work Items = COUNT('Bugs DevOps'[Work Items])
TOP 5 Workstream =
VAR Top5Cat =
CALCULATETABLE (
GENERATE (
VALUES ( 'Bugs DevOps'[Week] ),
TOPN (
5,
CALCULATETABLE ( VALUES ( 'Bugs DevOps'[Workstream] ) ),
[Conut Work Items]
)
),
ALLSELECTED()
)
RETURN
CALCULATE (
[Conut Work Items] * ( NOT ISEMPTY ( 'Bugs DevOps' ) ),
KEEPFILTERS ( Top5Cat )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@lordmukund , there is a blog around this - https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |