To get values in table which has more than 3% percentage

[Rocky6]

Hi All, 

I have  a small doubt in set analysis part. 

I am calculating a percentage as a measure and i am getting the percentage value with the below set analysis. 

In that percentage i need to show the values which are 3% more. below 3% should not been shown in the table. 

If(IsNull(Sum({<[Due Status]={"Overdue","Due"},Date={"$(vLatestDate)"}>}[Value1])
/
Sum({<[Year Agent] ={"$(vLastYear)"},[Month-Year],Date>}GWP)), 0, Sum({<[Due Status]={"Overdue","Due"},Date={"$(vLatestDate)"}>}[Value1])
/
Sum({<[Year Agent] ={"$(vLastYear)"},[Month-Year],Date>}Value))

This is the set i am using. Can you please help me out to bring the value which are more than 3%.

Thanks in advance.

[Gabbar]

You might need to aggregate it on a Dimension, because measure value depends on the respective Dimension/s.

Now lets Say after you expression is :-
Measure1
And have aggregated it on a Dimension , Dimension 1 like:-
aggr(Measure1,Dimension1)
Store this expression in a variable like:-
variable1 = aggr(Measure1,Dimension1)

then use a new set expression:-
If($(=variable1)>= '0.03',$(=variable1),0)
after this you can remove zero values using data handling in add on.

in addition to this you can also try this just for measure without aggregating it:-
where variable1 = measure1 and then the rest is same.

[Rocky6]

Hi Gabbar,

Thanks for your quick reply 

Can you please elaborate what should i write in the measure 1 part from my set analysis.  say my dimension will any city. 

for the second pat of my set analysis how do i store it. 

Thanks in advance

[Gabbar]

measure1 is your expression which you have written in the post,
and storing means : - Creating the variable and defining it, in its definition you can write the expression.

[Rocky6]

HI Gabbar ,

Thanks i have done that and i have created a new measure and added the if condition. 

For all the value which are less than 3% it is showing 0 and total value above the table also showing 0. 

How to remove this 0 from the measure. 

Thanks. 

[Gabbar]

go to add one in table and then Data handling, unselect include zero values.
Then For sum, go into the measure and scroll down, you will find total Function change that From auto to average.