8 Replies Latest reply on Sep 30, 2016 5:31 AM by Luciano Vasconcelos

# Top/Bottom N Issue

Hi Everyone,

I have done Top/Bottom N categories using some logic,its not working when i put segment in the color shelf(sheet 2) but its working fine when i don't use any other dimension in the color shelf(Sheet 1).

Actually it should work independent of what ever dimension we use in the color shelf.

Please let me know is it possible to resolve this issue from the logic which i have used.

Attached is the workbook

Thanks & Regards,

Rajesh L M

• ###### 1. Re: Top/Bottom N Issue

Hi Rajesh, why isn't it working as you expect? It looks like it's working correctly to me, unless you want to see the top 5 Sub-Categories X Segments when you put Segments on the colors mark. If you want this, then you need to edit the table calculation and put both Sub-Categories and Segments into the Addressing side.

• ###### 2. Re: Top/Bottom N Issue

Thanks David, I tried this but not working for me, if you don't mind can you pls attach your workbook.

• ###### 3. Re: Top/Bottom N Issue

Here it is. Note that I increased N to 10 to show that it's working.

• ###### 4. Re: Top/Bottom N Issue

Hi David, Phones is the top category here with \$330M but in your modified chart its not the case and home office segment is missing.

• ###### 5. Re: Top/Bottom N Issue

Ah, I see what you're looking for now. In this case, you'll have to use either LOD calculations or switch to using the Top tab in a dimension-based filter.

For the first approach, change your Top/Bottom Calc to this:

IF [Top/Bottom Sub Category]='Top' then

RANK_DENSE(SUM({FIXED [Sub-Category] : SUM([Sales])}),'desc') <= [Top/Bottom Sub Category Input]

ELSE

RANK_DENSE(SUM({FIXED [Sub-Category] : SUM([Sales])}),'asc') <= [Top/Bottom Sub Category Input]

END

For the second approach, just drop [Sub-Category] into filters and use the Top tab. You can still change N with a parameter, but you won't be able to switch between Top and Bottom using one.

• ###### 6. Re: Top/Bottom N Issue

Hey David,

Thanks for the logic but this doesn't work when i replace segment with State in the color shelf,its giving wrong output.

• ###### 7. Re: Top/Bottom N Issue

David Li Any luck David??

• ###### 8. Re: Top/Bottom N Issue

Hi.

You have to set up filter again.

Filter is based on older dimension color.