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

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

 

[sandy666]

2016 so will need to use get and transform

Power Query == Get&Transform

 

[ExGus]

I need it in a table I can then pivot on to get the channel, country revenue so that wouldn't work. it is doing the correct steps till the country gets added back in.

 

[sandy666]

Could you explain what are you doing with the result table after add M-code?

 

[ExGus]

a Pivot like this.. i have just copied the first few rows and have grouped some up by channel.
[TABLE="width: 292"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Deal amount[/TD]
[/TR]
[TR]
[TD]Nick Jr.[/TD]
[TD="align: right"]11,202[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD="align: right"]1,633[/TD]
[/TR]
[TR]
[TD]United Kingdom[/TD]
[TD="align: right"]1,173[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD="align: right"]1,170[/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD="align: right"]1,163[/TD]
[/TR]
[TR]
[TD]Thailand[/TD]
[TD="align: right"]1,103[/TD]
[/TR]
[TR]
[TD]Chile[/TD]
[TD="align: right"]1,097[/TD]
[/TR]
[TR]
[TD]Mexico[/TD]
[TD="align: right"]796[/TD]
[/TR]
[TR]
[TD]Indonesia[/TD]
[TD="align: right"]741[/TD]
[/TR]
[TR]
[TD]Poland[/TD]
[TD="align: right"]576[/TD]
[/TR]
[TR]
[TD]Hong Kong[/TD]
[TD="align: right"]520[/TD]
[/TR]
[TR]
[TD]Malaysia[/TD]
[TD="align: right"]399[/TD]
[/TR]
[TR]
[TD]Sweden[/TD]
[TD="align: right"]384[/TD]
[/TR]
[TR]
[TD]Switzerland[/TD]
[TD="align: right"]230[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]Russia[/TD]
[TD="align: right"]94[/TD]
[/TR]
[TR]
[TD]Comedy Central UK[/TD]
[TD="align: right"]7,668[/TD]
[/TR]
[TR]
[TD]Nickelodeon UK[/TD]
[TD="align: right"]6,760[/TD]
[/TR]
[TR]
[TD]Nickelodeon Spain[/TD]
[TD="align: right"]3,444[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

M-code from the post create group and sum total for channels by country so if you pivot this table it will give you wrong result

Query Table is the EndResult