topic Re: Pivot table filter measure based on % in App Development

Pivot table filter measure based on %

newuser — Sun, 24 Feb 2019 04:57:14 GMT

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.

Re: Pivot table filter measure based on %

ryo_okabe — Sun, 24 Feb 2019 10:07:30 GMT

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.

Re: Pivot table filter measure based on %

newuser — Sun, 24 Feb 2019 18:53:06 GMT

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)

Re: Pivot table filter measure based on %

newuser — Sun, 24 Feb 2019 19:47:08 GMT

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)