how to best summarize this in a pivot table?

[bigdan]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Province[/TD]
[TD]Postal Code[/TD]
[TD]Shipping Net[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ontario[/TD]
[TD]M4M 1L4[/TD]
[TD]$3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ontario[/TD]
[TD]M4M 1X5[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ontario[/TD]
[TD]M4M 3Z7[/TD]
[TD]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BC[/TD]
[TD]Y3L 1Z4[/TD]
[TD]-30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BC[/TD]
[TD]Y3L 1X5[/TD]
[TD]-300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BC[/TD]
[TD]Y3L 1M9[/TD]
[TD]-700[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have about 200K rows of transactions, to a whole set of postal codes. I'm trying to figure out which postal codes in which provinces are causing the biggest issues.

If I create a pivot table and put Province then Postal Code in the rows section, I get 1600 lines. Which is hard to get meaningful data from. I've put the Shipping Net (ie whether Shipping is profitable or making losses) in Values, and I'd like to filter the values. Either the bottom 10% or anything with losses of $2K or more. Is there a way to do this?

To clarify, I don't want just the top 10% of losses, since those could all belong to one province. I want the top 10% in each province.

I'd be grateful for any advice!

 

[Aryatect]

Hi, top or bottom 10 or 2 in the example below (not percent) can be attained using filter on Postal Code for top or bottom 10 this will yield you below results.

https://drive.google.com/open?id=1ZtQu9gsfex-xSAxtVqDjiWtfJl2N5bPK

https://drive.google.com/open?id=1maChFBRmYHds9zGkwKTVXCxn7mjcoarq

https://drive.google.com/open?id=1LmzfHJD_brtHLIzJnYNQdgKDX9t3bu9L

Hope this helps

 

[bigdan]

Thank you so much! So kind of you to go to all that trouble

So it seems like the key point was to do the pivot in Tabular Form?

Hi, top or bottom 10 or 2 in the example below (not percent) can be attained using filter on Postal Code for top or bottom 10 this will yield you below results.

https://drive.google.com/open?id=1ZtQu9gsfex-xSAxtVqDjiWtfJl2N5bPK

https://drive.google.com/open?id=1maChFBRmYHds9zGkwKTVXCxn7mjcoarq

https://drive.google.com/open?id=1LmzfHJD_brtHLIzJnYNQdgKDX9t3bu9L

Hope this helps

 

[Aryatect]

It should work in compact form also, key was putting filter on postal code column.

Glad could help and thanks for the feedback