Filtering with percentage parameters

[BMM27]

Hello,

I have to filter a matrix in two ways, by price and by percentage (%). When I filter by price, for example, from 0 to 250M to show all the items, it returns 80 rows, which is correct. However, when I filter by %, using -100% to 100%, it should return all the data but only returns 70 rows. Additionally, when I filter from -110% to 100%, it returns the 10 rows that were missing before but not the other 70. This issue occurs specifically with rows where % is 100%.

How it works:
I have created a parameter 

VarParam = GENERATESERIES(-1000, 1000, 0.001) 
I then filter a matrix with this using a top N filter, top 2000 by value on a "measure". This measure is: 

Check% =

IF(

    ROUND([%Round], 2) >= MIN('VarParam'[Parameter]) && ROUND([%Round], 2) <= MAX('VarParam'[Parameter]),

    ROUND([%Round], 2),

    BLANK()

)

where %Round =:

 

IF([%] >= 1, 1, IF([%] <= -1, -1, [%]))

and:

% = 

CALCULATE(1-(CALCULATE(

                SUM(Table[amount]),

                TREATAS(VALUES(TableB[id]), Table[id]),

                TREATAS(VALUES(TableB[ID2]), Table[Group]),

                Table[category] <> 0104

            )/[compAmount]), TREATAS(VALUES(TableB[sonID]), TableC[id]))

If you help me I will appreciate, thank you!

[rajendraongole1]

Hi @BMM27 - %Round that will allow proper filtering for exact percentages

%Round =
IF([%] > 1, [%], IF([%] < -1, [%], ROUND([%], 2)))

After updating the %Round measure, modify your Check% measure to accommodate the updated logic and ensure it's working correctly within the given parameter range.

Check% =
IF(
[%Round] >= MIN('VarParam'[Parameter]) && [%Round] <= MAX('VarParam'[Parameter]),
[%Round],
BLANK()
)

Try temporarily removing the TOP N filter and checking the result. If the filter is excluding the rows with exactly 100%, you might need to reconsider how you're applying the filter and the ranking logic.

Remove the TOP N filter.Adjust the rounding logic to include exact 100% and values greater than 1.Test with various ranges in the VarParam.

Hope this works

[BMM27]

Hi @rajendraongole1 , I changed the measures and params following your instructions, but It doesn't work.
And I can confirm that when I filter from -1 to 1, it doesn't return any rows with 100%. And the strane thing is that when I filter from -1 to 1.1, it only returns the rows with 100%.

[rajendraongole1]

Hi @BMM27 - can you try below calculations

%Round = IF([%] >= 1, 1, IF([%] <= -1, -1, [%]))

Since the filtering behavior changes when expanding the upper limit

Check% =
IF(
[%Round] >= MIN('VarParam'[Parameter] - 0.001) &&
[%Round] <= MAX('VarParam'[Parameter] + 0.001),
[%Round],
BLANK()
)

or 

Check% =
IF(
[%Round] >= MIN('VarParam'[Parameter]) &&
[%Round] <= MAX('VarParam'[Parameter]) || [%Round] = 1,
[%Round],
BLANK()
)

You can cast the parameter to ensure compatibility:

VarParam = GENERATESERIES(-1000.0, 1000.0, 0.001)

The most likely cause of the issue is how the rounding logic and filtering interact, especially around 100% (1.0).

[BMM27]

Hi @rajendraongole1 still not working, I'm trying something that doesn't make sense...

%Round= IF([%] >= 1, -0.001, IF([%] <= -1, -100, ROUND([%], 2)))
It partially works, what isn't returning now is everything below 0.01 or 1%. What do you think? I'm playing a little putting different values there

[BMM27]

%Round= IF([%] >= 0.9, -0.9, IF([%] <= -1, -1, ROUND([%], 2)))

This it worked but I don't think this is the correct solution... But works! If anyone has the correct solution please write below, thanks!

[Anonymous]

Hi @BMM27 ,

It doesn't look like there's a better way to do it, thanks for sharing!

Best regards,
Community Support Team_ Scott Chang