Filter values in a pivot table

[ExGus]

Hi,

I am struggling to filter Values in this example of a pivot table.
I want to remove all channels from the pivot that are less than $100 in total not by country. i.e. when I apply the filter I should see some country channels line in this data that are less than 100 because that channel will be on another country and in total they are over 100. I have tried using value filters without success.
i have only shown part of the data there are many more rows of data for more countries.
Any ideas how I might be able to do it?

thank you

Sum of DELIVERED $ NET
Country	Channel Name	Total
Australia	Comedy Central UK	1,521.88
Australia	Nickelodeon UK	1,134.46
Australia	Nick Jr.	1,081.84
Australia	MTV International	295.19
Australia	Channel Does not exist	187.16
Australia	Bellator MMA	148.59
Australia	Comedy Central Spain	127.82
Australia	MTV Shores	88.98
Australia	Nick Jr. Россия	70.41
Australia	Nickelodeon Spain	31.13
Australia Total		4,687.46
Austria	Nickelodeon DE	590.34
Austria	Comedy Central DE	442.74
Austria	MTV Germany	201.56
Austria	Comedy Central UK	197.32
Austria	Nickelodeon UK	138.68
Austria	Nick Jr.	118.68
Austria	Comedy Central Spain	35.31
Austria	MTV International	31.11
Austria	Nickelodeon IT	30.23
Austria	Nick Jr. Россия	26.03
Austria Total		1,812.01
Brazil	Nickelodeon PT	7,382.33
Brazil	Nick Jr.	6,156.14
Brazil	Comedy Central BR	1,022.42
Brazil	Nickelodeon Spain	862.39
Brazil	Nickelodeon UK	719.88
Brazil	Nick Jr. Россия	688.35
Brazil	Nickelodeon IT	550.23
Brazil	Nickelodeon France	519.81
Brazil	MTV Brasil	382.80
Brazil	Nickelodeon DE	159.85
Brazil Total		18,444.20
Chile	Nick Jr.	1,578.03

<tbody>
</tbody>

 

[sandy666]

but which one?

 

[ExGus]

Greater than 100

 

[sandy666]

with PowerQuery aka Get&Transform

M-code:

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Channel Name", type text}, {"Country", type text}, {"DELIVERED $ NET", type number}}),
    RplErr = Table.ReplaceErrorValues(Type, {{"Channel Name", null}}),
    Group = Table.Group(RplErr, {"Channel Name"}, {{"Delivered & Net", each List.Sum([#"DELIVERED $ NET"]), type number}, {"Count", each _, type table}}),
    GreaterThan100 = Table.SelectRows(Group, each [#"Delivered & Net"] > 100),
    List = Table.AddColumn(GreaterThan100, "Country", each List.Distinct(Table.Column([Count],"Country"))),
    ExpandCountry = Table.ExpandListColumn(List, "Country"),
    Reorder = Table.ReorderColumns(ExpandCountry,{"Country", "Channel Name", "Delivered & Net", "Count"}),
    Sort = Table.Sort(Reorder,{{"Country", Order.Ascending}})
in
    Sort[/SIZE]

 

[ExGus]

Thanks, can i just copy that code into VB and run as a macro on the data?

 

[sandy666]

NO!

as I said this is PowerQuery (Get&Transform) not any vba

what is your Excel version?

 

[ExGus]

ok thanks will look that up

 

[sandy666]

sure

have a nice day

 

[ExGus]

2016 so will need to use get and transform

 

[ExGus]

In the expand country step it is doing something odd, it is duplicating the total for the channel Sum and then applying that number to each row when country is brought in so ending up with a much bigger number,

 

[sandy666]

so maybe this layout will be better

Channel Name	Delivered & Net	Country
Comedy Central UK	926.12​	Poland, Brazil, Switzerland, Austria, Mexico, Chile
Nickelodeon PT	761.67​	Mexico, Chile, Russia, Malaysia, Thailand, France, Poland, United Kingdom, Sweden, Switzerland, Austria, Australia, Hong Kong
Nick Jr. ??????	1095.16​	Thailand, Poland, France, Mexico, Malaysia, United Kingdom, Sweden, Australia, Hong Kong, Switzerland, Austria
Bellator MMA	1003.32​	Brazil, Malaysia, Thailand, United Kingdom, France, Australia, Sweden, Poland, Hong Kong, Germany, Mexico, Chile, Austria, Switzerland
Nickelodeon DE	881.87​	Switzerland, Russia, Thailand, Malaysia, Poland, France, Chile, Mexico, United Kingdom, Sweden, Australia, Hong Kong
Nickelodeon France	763.76​	Russia, Malaysia, Thailand, Chile, Mexico, Poland, Switzerland, United Kingdom, Sweden, Austria, Australia, Hong Kong
MTV IT	408.37​	Indonesia, Switzerland, Malaysia, France, Thailand, Russia, Germany, Brazil, United Kingdom, Poland, Sweden, Austria, Mexico, Hong Kong, Chile, Australia
Nick Jr.	583.04​	Hong Kong, Switzerland, Austria
Comedy Central Spain	958.93​	Mexico, Indonesia, Russia, Malaysia, Brazil, Australia, United Kingdom, Sweden, Thailand, France, Poland, Germany, Chile, Austria, Switzerland, Hong Kong
MTV International	970.52​	Malaysia, Australia, Russia, Sweden, Thailand, Poland, France, Brazil, Germany, Hong Kong, Mexico, Austria, Switzerland, Chile
Channel Does not exist	841.21​	United Kingdom, Malaysia, Australia, Sweden, Thailand, France, Russia, Brazil, Germany, Hong Kong, Poland, Switzerland, Austria, Indonesia, Mexico, Chile
Nickelodeon IT	882.54​	Russia, France, Thailand, Mexico, Malaysia, Chile, Switzerland, Poland, United Kingdom, Sweden, Austria, Australia, Hong Kong
MTV Shores	686.67​	United Kingdom, Indonesia, Brazil, France, Australia, Poland, Malaysia, Sweden, Germany, Thailand, Russia, Switzerland, Austria, Hong Kong
MTV FR	301.72​	France, Malaysia, Thailand, Russia, Mexico, Brazil, Switzerland, Poland, United Kingdom, Germany, Australia, Chile, Sweden, Austria, Hong Kong
Nickelodeon UK	504.76​	Hong Kong, Austria, Switzerland
MTV Germany	444.39​	Austria, Switzerland, Indonesia, Poland, Russia, France, Malaysia, United Kingdom, Thailand, Australia
Comedy Central DE	299.85​	Switzerland, Thailand, Poland, France, Russia, Indonesia, Sweden, Australia, United Kingdom, Brazil, Hong Kong, Malaysia, Chile, Mexico
Nickelodeon Spain	633.94​	France, Malaysia, Poland, Thailand, United Kingdom, Sweden, Switzerland, Austria, Australia, Hong Kong
Big Brother UK	151.36​	Indonesia, United Kingdom, Malaysia, Thailand, Russia, Hong Kong, Brazil, Australia, France, Poland, Sweden, Germany, Mexico, Chile, Austria, Switzerland
MTV PL	185.02​	Malaysia, Thailand, Russia, Brazil, France, United Kingdom, Germany, Sweden, Mexico, Austria, Hong Kong, Switzerland, Chile, Australia
Nickelodeon ??????	168.96​	Brazil, Indonesia, Thailand, Malaysia, France, Poland, Chile, Mexico, United Kingdom, Sweden, Australia, Austria, Hong Kong, Switzerland
Nickelodeon NL	107.36​	Brazil, Indonesia, Russia, France, Thailand, Mexico, Sweden, Malaysia, United Kingdom, Chile, Poland, Austria, Australia, Switzerland, Hong Kong