Top N per group

[omillzy]

Case_Category__c	Department	# Cases
Ad hoc invoice/ credit note requests	Billing	51
Contract queries	Billing	4
Development invoice/ credit note requests	Billing	2
Invoice/ credit note release requests	Billing	5
Invoice/Credit note Queries	Billing	46
Other	Billing	19
Refund requests	Billing	3
Allocation queries	Cash - General	14
Bank detail info request	Cash - General	1
Other	Cash - General	22
Remittances	Cash - General	5
Specific transaction enquiry	Cash - General	5
Ad hoc requests	Cash - Notifications	33
Change of bank details	Cash - Notifications	10
Expenses forms	Cash - Notifications	64
Expenses queries	Cash - Notifications	10
Other	Cash - Notifications	4
Remittances	Cash - Notifications	5
Soldo top up requests	Cash - Notifications	8

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

[V-pazhen-msft]

@omillzy 

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

[omillzy]

@V-pazhen-msft thanks for your response,

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

[Anonymous]

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]

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

If i solve your problem, Mark it as solution.

Thanks,

Pravin

[omillzy]

@Anonymous This doesn't seem to work. I have created a dummy table (called Dummy), with one column (called Input) going from numbers 1-20 and created the Rank measure as you've shown, when I put this as a visual filter, or into the table I get the following error:

[omillzy]

@Anonymous Ok i had to convert the Dummy[Input] column to type text, I've done that and it's just giving me a rank of 0 for everything

[Anonymous]

have you selected anything is input slicer?

If not then select any value in Slicer else update selectedvalue function.

[Anonymous]

in*

Same measure is working in my case . We have same client requirement.

[Anonymous]

Sorry I forgot to mention. ADd measure in visual level filter as well and set measure is not zero.

They u can test for top N values by selecting different values in input dropdown

[omillzy]

@Anonymous No it just doesn't work, there should only be 3 entries per reason

Here's my code for the measure in case I am doing something wrong?

[Anonymous]

Could you share your dataset? (www.linkedin.com/in/pravin-p-wattamwar)

Please check on your relationship(one to many or many to one)

[Anonymous]

Update measure

Rank =

var a =RANKX(ALLEXCEPT(Data,Data[Department]),[#Cases],[#Cases],DESC,Dense)

var b =SWITCH(SELECTEDVALUE(Input[Input]),"1",1,"2",2,"3",3,"4",4,"All",99)

return

if(a<=b,1,0)

[Anonymous · Accepted solution]

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

[omillzy]

@Anonymous Yes I have added the Input slicer and selected 1, 2 & 3.

Perhaps it works for you but not me because the actual data is different, in the original post, I gave the summarised table, my raw data looks like this:

So each row has a whole load of data associated with a case, including the department and a category.

Dept.	Case_Category__c
Billing	Ad hoc invoice/ credit note requests
Billing	Other
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Other
Billing	Other
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Other
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Other
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Contract queries
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Other
Billing	Invoice/Credit note Queries
Billing	Other
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Other
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Other
Billing	Other
Billing	Other
Billing	Ad hoc invoice/ credit note requests
Billing	Other
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Refund requests
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Other
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Other
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Refund requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Refund requests
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Other
Billing	Other
Billing	Ad hoc invoice/ credit note requests
Billing	Contract queries
Billing	Ad hoc invoice/ credit note requests
Billing	Development invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Other
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/ credit note release requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Development invoice/ credit note requests
Billing	Contract queries
Billing	Invoice/Credit note Queries
Billing	Other
Billing	Invoice/Credit note Queries
Billing	Invoice/ credit note release requests
Billing	Invoice/ credit note release requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Contract queries
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/Credit note Queries
Billing	Ad hoc invoice/ credit note requests
Billing	Invoice/ credit note release requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Ad hoc invoice/ credit note requests
Billing	Other
Billing	Invoice/ credit note release requests
Cash - General	Other
Cash - General	Allocation queries
Cash - General	Allocation queries
Cash - General	Other
Cash - General	Other
Cash - General	Other
Cash - General	Other
Cash - General	Other
Cash - General	Allocation queries
Cash - General	Allocation queries
Cash - General	Other
Cash - General	Allocation queries
Cash - General	Other
Cash - General	Other
Cash - General	Other
Cash - General	Remittances
Cash - General	Remittances
Cash - General	Remittances
Cash - General	Other
Cash - General	Allocation queries
Cash - General	Allocation queries
Cash - General	Allocation queries
Cash - General	Other
Cash - General	Other
Cash - General	Allocation queries
Cash - General	Other
Cash - General	Specific transaction enquiry
Cash - General	Specific transaction enquiry
Cash - General	Other
Cash - General	Specific transaction enquiry
Cash - General	Allocation queries
Cash - General	Other
Cash - General	Remittances
Cash - General	Allocation queries
Cash - General	Other
Cash - General	Other
Cash - General	Other
Cash - General	Specific transaction enquiry
Cash - General	Allocation queries
Cash - General	Other
Cash - General	Other
Cash - General	Bank detail info request
Cash - General	Allocation queries
Cash - General	Other
Cash - General	Specific transaction enquiry
Cash - General	Remittances
Cash - General	Allocation queries

[Anonymous]

it should be single select not multi select.

Select only one value in input slicer.

[omillzy]

Hmm @Anonymous , ok I have selected just '1' on the input slicer - and now the Rank is pulling values through, but it's not showing the top 3. Strangely, it doesn't even pick up the top number: 116

[omillzy]

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)