5 Replies Latest reply on Apr 3, 2018 7:45 AM by Michael Hesser

# Filtering \$ Amounts from Largest to Smallest within 2 Countries

I am trying to figure out how I need to go about creating a filter that can apply to sorting \$ amount from largest to smallest in each individual country and not only to the overall \$ amount. Below in the picture Supplier A has the largest \$ spend amount in the US but not in Canada. In Canada, Supplier B has the largest spend amount but does not in the US. Is there a way that I can apply this to only sort each individual country and not overall \$ amount?

• ###### 1. Re: Filtering \$ Amounts from Largest to Smallest within 2 Countries

hello,

Create a measure : RANK(SUM(Total Delivered))

Right click go to compute using --> Pane down.

This will give your desired result.

Below is the sample from superstore data

• ###### 2. Re: Filtering \$ Amounts from Largest to Smallest within 2 Countries

Hi Chase;

There are many different ways to get the results you're after: it's a little more difficult because I don't know what your data looks like.

One way is to just sort by negative sales:

• Make a calc

SortSales

-[Sales]

• Drop onto rows
• Change it into Discrete
• Move the now blue pill BETWEEN Plant and Supplier Parent
• Hide it by de-selecting Show in Header
• Your sheet should now be sorted by Plant Country, by Sales Rank for Supplier Parent for the Plant Country, by Plant name

Easy!

Other option is to rank them:

• Create a rank calculation. You can do this as quick calc, or make your own formula:

RANKER

RANK(SUM([Sales]))

• Change the Default Table Calculation so that results are computed along Supplier. In my example, they're done via Category.

• Drop RANKER into rows
• Transform it into Discrete
• Move the now blue pill BETWEEN Plant and Supplier Parent
• Hide it by de-selecting Show in Header
• Your sheet should now be sorted by Plant Country, by Sales Rank for Supplier Parent for the Plant Country, by Plant name

My results, using Superstore, look like this:

Good luck!

• ###### 3. Re: Filtering \$ Amounts from Largest to Smallest within 2 Countries

Solution verified! Thanks!

• ###### 4. Re: Filtering \$ Amounts from Largest to Smallest within 2 Countries

Thanks for the quick response!

• ###### 5. Re: Filtering \$ Amounts from Largest to Smallest within 2 Countries

We're here to help!