Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Top N per group

Case_Category__cDepartment# Cases
Ad hoc invoice/ credit note requestsBilling51
Contract queriesBilling4
Development invoice/ credit note requestsBilling2
Invoice/ credit note release requestsBilling5
Invoice/Credit note QueriesBilling46
OtherBilling19
Refund requestsBilling3
Allocation queriesCash - General14
Bank detail info requestCash - General1
OtherCash - General22
RemittancesCash - General5
Specific transaction enquiryCash - General5
Ad hoc requestsCash - Notifications33
Change of bank detailsCash - Notifications10
Expenses formsCash - Notifications64
Expenses queriesCash - Notifications10
OtherCash - Notifications4
RemittancesCash - Notifications5
Soldo top up requestsCash - Notifications8


I want to show the top 3 case category's, by department. I've tried all sorts of ranking measures but none seem to work. The # cases column is a measure, counting the case ID in my case table

 

Any help would be appreciated

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Now It's working as per your expectation.

    Rank2 = var a = RANKX(ALLSELECTED('Case'[Case_Category__c]),[# Cases],[# Cases],DESC,Dense) var b = SWITCH(SELECTEDVALUE(Dummy[Input]),"1",1,"2",2,"3",3,"4",4,"All",99) return if(a<=b,1,0)

     

     

    Make sure here we are checking for first 4 and 'all' as 5th input.  Just modify var b as per your requirement

     

    Thanks & Regards,

    Pravin

17 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    So for example, for 'Billing' department, I want a measure (or visual filter) that can be used on a bar chart so I only see 'Ad hoc invoice/ credit note requests', 'Invoice/Credit note Queries' and 'Other' - the top 3 (by # cases)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

    1. I ranked the measure #Case by department using the following DAX: 

     

    Rank Measure = RANKX( ALLEXCEPT('Table', 'Table'[Department]), [#Case measure],, DESC)

     

    2. The next step is to set a visual level filter to the Rank measure: 



    Paul,
    Best

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Ceate one dummy table with input column.

      Input

      1

      2

      .

      ..

      ..

      20

       

      Use this column for accepting N input.

      Then create below measure.

       

      Rank =
      var a =RANKX(ALLSELECTED('table'Department),[#cases],,DESC,Dense)
      var b =SWITCH(SELECTEDVALUE('Dummy'[Input]),"1",1,"2",2,"3",3,"4",4,"5",5,"All",99)
      return
      if(a<=b,1,0)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Then add measure in visual level filter and set rank is not 0.

         

        If i solve your problem, Mark it as solution.

         

        Thanks,

        Pravin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous thanks for your response,

      This is what I have tried - but strangely it doesn't rank as expected, see below.