1 2 Previous Next 21 Replies Latest reply on Jul 25, 2019 9:41 AM by Hari Ankem

# Showing top n people with highest category A and their lowest category in one sheet

I want to show the top n(5/10) people with highest category (lets say A) and also show their lowest and lowest only (but not 0) category amount as well. And this should be filtered by category name.

I attached a dummy workbook where i got the highest category shown and i also know how to use index to show the top (5/10). But my problem is i cant get the lowest category to up as well.

Thanks!

• ###### 1. Re: Showing top n people with highest category A and their lowest category in one sheet

You are probably looking for an output like this:

Here are the 2 calculated fields I have created:

1. Rank by Sales:    RANK(SUM([Sales]),'desc')

2. Filter Ranks:

[Rank by Sales]<=[Top Customers]

OR

[Rank by Sales]>=WINDOW_MAX([Rank by Sales])-[Top Customers]

And to exclude the zero sales values, you can add it to a filter.

Hope this helps. The updated workbook is attached.

P.S. The dummy workbook you had attached did not have sufficient data to look at the Top 5/10. So, I have used the Superstore data set to show you the possible solution.

• ###### 2. Re: Showing top n people with highest category A and their lowest category in one sheet

Hi,

Thank you for responding. I am looking for something slightly different. Graph or chart alone the lines that;

Ideally we can filter by category. And if you filter to category A, then it shows the top 10 people with the highest sales in category A and whatever lowest sales amount category for each person is.

• ###### 3. Re: Showing top n people with highest category A and their lowest category in one sheet

Once you have the main logic and filter criteria set, you need to build on it.

• ###### 4. Re: Showing top n people with highest category A and their lowest category in one sheet

Thanks for following up. I have gotten to the point where my chart shows similar content to yours. But what i am having problem is showing the lowest category for the same person.

• ###### 5. Re: Showing top n people with highest category A and their lowest category in one sheet

Like for Keith Herrera, he has the highest sales in furniture. But which category does he have the lowest sales on? I want to show that in the same chart.

• ###### 6. Re: Showing top n people with highest category A and their lowest category in one sheet

That's a complete different requirement. You will need to use LODs as shown below to get the category for the highest and lowest sales.

• ###### 7. Re: Showing top n people with highest category A and their lowest category in one sheet

This is exactly what i wanted! Thank you!

• ###### 8. Re: Showing top n people with highest category A and their lowest category in one sheet

You are welcome.

• ###### 9. Re: Showing top n people with highest category A and their lowest category in one sheet

Hi I actually have more questions related to this topic once i used my real data lol. In my real dataset, sum of some of the categories is 0. I would like my lowest sales category to show the lowest non-zero number. How should i tweak the formula?

• ###### 10. Re: Showing top n people with highest category A and their lowest category in one sheet

See if adding the SUM([Sales]) as a filter works:

Or, see if this works:

{FIXED [Customer Name]:MAX(

IF {FIXED [Customer Name],[Category]:SUM([Sales])}={FIXED [Customer Name]:MIN(

IF {FIXED [Customer Name],[Category]:SUM([Sales])}>0 THEN {FIXED [Customer Name],[Category]:SUM([Sales])} END

)}

THEN [Category]

END

)}

• ###### 11. Re: Showing top n people with highest category A and their lowest category in one sheet

is the formula for highest category sales or lowest category sales

• ###### 12. Re: Showing top n people with highest category A and their lowest category in one sheet

Lowest, because you asked for change in that only.

• ###### 13. Re: Showing top n people with highest category A and their lowest category in one sheet

Thank you!

• ###### 14. Re: Showing top n people with highest category A and their lowest category in one sheet

Hi i have a follow up question. I got the highest and lowest sales category using your method. But now i also want Janet's sales percentage out of the departments sales for the highest and lowest category. I an new to LOD and tried to write a calc field           {fixed [Highest platform]:sum([Department sales])}/sum([Janet's sales])           but it is not working. Can you show me how i can get there? Thank you! I also attached the workbook.

1 2 Previous Next