Show values greater than or less than in Pivot Table

[Anonymous]

I have a Pivot Table with Few Dimensions and the following Expressions as Columns

sum(Profit)

sum(Variance)

sum(Derived)

which are numbers

I have to create a button where if I select the button, the table must show only values greater than 2,00,000 and another button for 5,00,000.

How do I do it?

Is it possible to create three buttons for each column?

[sunny_talwar]

You can use variables and then based on it you can set a condition for your expression. Which of the three expression does this variable need to control?

[sunny_talwar]

Check if the attached sample helps:

[Anonymous]

Hi Sunny,

Thank you. I used the same approach. What happens if I want to apply the same filter rule (>2k or 5K) on all columns in the pivot?

[sunny_talwar]

Use the same if statement for all your expressions

[Anonymous]

Thanks Sunny. That was just a sample expression. The original expressions that I am using is

sum({$<Month = {'January 2016'},LOB={'Bank'}>} Profit)

The Expression which you have used in the qvw is

If(Sum(Measure) > vValue, Sum(Measure))

How do I write my expression in this case?

[sunny_talwar]

Like this:

If(Sum({$<Month = {'January 2016'},LOB={'Bank'}>} Profit) > vValue, Sum({$<Month = {'January 2016'},LOB={'Bank'}>} Profit))

[Anonymous]

Oh, so what I thought is right. We need to repeat the analysis again. Thanks Sunny. I will try this and let you know if works for my requirement

[sunny_talwar]

There might be other ways, but unless I see a sample to look at, difficult to say what might work

[Anonymous]

Hi Sunny,

Thanks for the solution. I am currently using the same technique in my dashboard. The problem arises when there are -ve values and 0 in the field. I have attached the same qvw file for your understanding.

Issues:

1. You can see that the button doesn't work if there are -ve values and 0s in the table.

2. if there are two measures as expressions, selecting the button shows values >200 in Measure 1 column but I can still see other Dimensions as they have values Measure2 column. I want to show only those dimensions whose sum(measure) value is >200 or >500.