2 Replies Latest reply on Oct 25, 2016 11:31 AM by Brittney Parker

# Sorting: Largest to Smallest/ Subtotals and Grand Totals

Hi All,

I am working with zip codes and mapping them according to their Held Revenue. I have successfully mapped the zip codes but am wanting to customize the action I have made when selecting the state on the map. Here is a clip of what I have so far on my dashboard.

Action: When a state isn’t selected it show the held revenue to the right from largest to smallest. When I click on a state the held revenue is populated from largest to smallest as well.

On my worksheet for the data I have created an AGG field to sort the held revenue this way by using the following formula:  SUM([Held Revenue Amount])*-1  I found this formula when digging around and it works fine but I’m needing a little something more.

What I am wanting is for the held revenue to stay in the order they are in when I place another dimension behind the zip code. I know it has something to do with the “Held Revenue Sorter” but I don’t know enough to make the sorting formula fixed or to create something else.

What I’m hoping for is to have all of the phone numbers associated with each zip populated to the right all while showing the sub total for each zip code with the grand total at the end. I provided an example of what I’m looking for below. (Note*: In the attached workbook I have included random 9 digit numbers to represent the phone numbers.)

Ex:

 California 96001 5697706263 \$430.40 1807875279 \$390.16 6105788887 \$330.55 3490560261 \$316.27 1151320003 \$300.62 573937443 \$275.27 Etc…. Sub Total \$39,493.35 California 94087 4506769575 \$344.11 2020268061 \$264.71 4600548682 \$239.47 Etc… Sub Total \$19,245.61 Etc… Etc… Etc… Grand Total \$316,504.84

And so on.

Is this possible? Any help would be greatly appreciated!

Regards,

Brittney

• ###### 1. Re: Sorting: Largest to Smallest/ Subtotals and Grand Totals

Brittney,

Is this what you are looking for ?

I created three calculated field for the Sorters.

One for the States sums.

{FIXED [State]:SUM([Held Revenue])}

One for the Zip sums.

{FIXED [State], [Zip Code]:SUM([Held Revenue])}

And one for the phone number sums.

{FIXED [State], [Zip Code], [Phone Number]:SUM([Held Revenue])}

I placed them on the Detail shelf, remove the original sorter, add Phone number on the Rows.

To set the sorting, right-click on the the blue pills  State-Sort , Zip Code-Sort and  Phone Number-Sort.

And right-click State-Subtotals and ZipCode-Subtotals to get the totals.

I also set the totals on top but this is configurable through Analysis-Totals

This way States are sorted by SUM(Held Revenue), within each State  Zip Codes are sorted by SUM(Held Revenue) and within each Zip  phone numbers are sorted by SUM(Held Revenue)

see in the attached

Michel

1 of 1 people found this helpful
• ###### 2. Re: Sorting: Largest to Smallest/ Subtotals and Grand Totals

Thank you so much, Michel! This is exactly what I am looking for! I really appreciate you taking the time to create this for me and helping me learn as well.

Thanks again!

Brittney