Forum Discussion

lordmukund's avatar
lordmukund
Microsoft Employee
5 years ago
Solved

Show TOP N Values for each category

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 ...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

v-kkf-msft's avatar
v-kkf-msft
Community Support
5 years ago

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.