6 Replies Latest reply on Sep 11, 2017 3:44 AM by Sreehari Katageri

# Top N using Set, within a subcategory

Hi All,

I want to view the top N of a measure based on each subcategory. For example, the dimension hierarchy could go like this:

1. Channel

2. Region

3. Store Number

I want to see all of the top stores of each region, or the top regions of each channel. But when I create a set on region or stores, it aggregates all and chooses from that population. How do I set a condition to only look within each category, sort of like a table calculation?

Sample workbook is attached.

• ###### 1. Re: Top N using Set, within a subcategory

Have you checked this.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 2. Re: Top N using Set, within a subcategory

Hi Alec,

Here you go. Let me know if this is what you were looking for. I made modification to your calculated fields. Percentage and ranking is done for each channel.

https://public.tableau.com/views/Regional_Performance_Test_2/Sheet2?:embed=y&:display_count=yes&publish=yes

Thanks,

Mani

1 of 1 people found this helpful
• ###### 3. Re: Top N using Set, within a subcategory

Thanks Deepak, I tried the index function, which only works to an extent. How would you tie that to a parameter to only show n records?

Mani, this looks to be the desired outcome. I can't seem to download the workbook, however. Could you please attach as a packaged workbook?

• ###### 4. Re: Top N using Set, within a subcategory

Hi Alec,

Here you go. Please mark it has helped so that the ticket is closed.

Thanks,

Mani

• ###### 5. Re: Top N using Set, within a subcategory

Hi Mani,

I think you only attached a screenshot. Could you attach the packaged workbook?

I found a solution by creating a boolean formula based on parameters and put the field into the filters:

This shows top 3, bottom 3, and all:

IF [Parameters].[PERFORMERS]=3 and [Parameters].[Metric]= 1

THEN (RANK([COUNT SALES WTD BUDGET VAR %],'desc')<[PERFORMERS]+1)

ELSEIF [Parameters].[PERFORMERS]=2 and [Parameters].[Metric]= 1

THEN (RANK([COUNT SALES WTD BUDGET VAR %],'asc')<[PERFORMERS]+2)

ELSEIF [Parameters].[PERFORMERS]=4 and [Parameters].[Metric]= 1

THEN (RANK([COUNT SALES WTD BUDGET VAR %])<[PERFORMERS]+100)

• ###### 6. Re: Top N using Set, within a subcategory

Hi I need an urgent help. Am building a scatter plot in tableau with the measures say Profit % and discount %. Then I added a dynamic reference line through the parameter for both x and y axis. In the detail section I have sales order Number. What I need it when I change the reference line through parameter the respective quadrant should highlight with red color for the condition (Profit % < Reference line of Profit  and discount % > Reference line of discount) . So basically I want to highlight where we are giving high discount and getting less profit.

Sorry I could not share the data with you.

In the above image I need red circles only in the lower left quadrant as it says profit % <35% & Discount%>58%