Tableau: Return Top N departments for sites according to maximum sales value

Question

Hi So I have a table where I have data like this

Site     Dept  Sales  Orderno
Site1   D1       100      1
Site1   D1      200       2
Site1   D1      300       3
Site1   D2     1100       4 
Site1   D2     2200       5
Site1   D2     1300       6
Site2   D3     1100       7
Site2   D3     2200       8
Site2   D3     3000       9
Site2   D4     1000       10
Site2   D4     2000       11
Site2   D4     3000       12

So for e.g. here i want to see only the top N dept for each site according to sum(sales)

N=1 here , but need a generalised formula of sorts since N can be anything

For site 1 , dept 2 has more sum(sales ) and for site 2 dept 4 has more sales overall , so only those should be returned in a line chart. How to do this

Answer 1

That's a tricky one (at least for novices like me)

Steps-

1. Add calculated field named 'Index' as

INDEX()

2. Change this calculated field to 'discreet'

3. Add Site, Index and then Dept to rows, and Sum of sales to visualisation

4. Right Click Index (field) --> Edit Table Calculation --> Click 'Specific Dimensions' in 'Compute using' --> select 'Site' from drop-down of 'restarting every' option --> under 'Sort order' click 'custom' --> select 'sales' and 'sum' and descending respectively --> click OK (Cross above)

5. Control and drag Index to Filter. Click OK

6. Change index filter to continuous (by right clicking it)

7. Select your 'N' as desired

8. Optionally drag Site pill to Colors

I think this is the view desired.