Pivot table filter measure based on %

[newuser]

Hello - In the example below, if I wanted to filter for CategoryName having % North America > 20%, is that possible? I was trying to see if set analysis would help this filter, but I couldn’t see how. Then I saw some other posts about filtering pivot tables, but that was based on a dimension or column. Here I need to filter a pivot table based on a measure value for a  specific column value (in this case, North America  20%) … If that makes any sense. Thanks.

sense.

[ryo_okabe · Accepted solution]

Hi,

There two ways to filter measure based on %.

One is to use filterpane,the other is use aggr function and set Analysis in dimension.

I attached sample qvf. Please check this.

[newuser]

Thanks for your feedback. Question on the .qvf: in the second pivot table, I expected the %'s to add to 100% across? They don't? In fact, I expected the %'s across for the filtered rows to be the same as what shows in the 1st pivot table above, for those same filtered rows?

Secondly, if year was an additional column (1st level column), then I assume the same set expression syntax could be applied with minor tweak below? Added "yr":

IF(
 Aggr(nodistinct
     Sum({<Division={'North America'}>}Share)/Sum({<Division={'North America'}>} total <Division, yr> Share)
 ,Category)>=0.2,
Category)

[newuser]

Got the sample to work by the following revised dimension. This way, the row adds to 100% across. And if I had two columns, I would add the second column separated by a comma where <Category> is listed in the expression below. Thanks for your earlier help!!

IF(
 
     AGGR(nodistinct Sum({<Division={'North America'}>}  Share)/Sum(total <Category> Share), Category)
 >=0.20,
Category)