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   --> "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])  )

 
But I am not getting the Top 5 Legend (Workstream) values instead I am getting all the workstreams. Can someone please help me with the DAX in the measure if I am making some mistake in the above DAX formula.
PFB snips.
 

 
 
 
 

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.

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.

2 Replies

  • 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.