Forum Discussion

k3rz0rg's avatar
k3rz0rg
Icon for Advocate III rankAdvocate III
2 years ago

Unable to filter by decimal number!

Hi everyone, 

 

I was working on a table which has a variance which is a 1 decimal % column 

Now, when I try to filter the records from the filter pane setting show me the data greater than 0 or 0.0, it does not work, ie.

If I tried 0.01, that changes to 1% and filters out the 0.0 rows but that also filters out the rows from 0.1 to 0.9 as well which I don't want, I want only the rows which are not 0 variance.

the formula for the variance is IFERROR (((Price1-Price2) / Price1), 0), I tried changing that to 0.0 or 0.00 but no luck.

Am I missing something? Any info/help would be grately appreciated.

 

Thanks.

 

Need Help

6 Replies

  • LeandroDeodato's avatar
    LeandroDeodato
    Icon for Resolver II rankResolver II
    2 years ago

    grow up the decimal houses in variance, i think this number isn't 0, its like 0,00000001%

    • k3rz0rg's avatar
      k3rz0rg
      Icon for Advocate III rankAdvocate III
      2 years ago

      Thank your for your reply. The more I increase the 0s after the decimal, the number becomes bigger automatically. For example, if I set 0.01, it becomes 1.0%, if 0.001 then 10.0% automatically. 

      • LeandroDeodato's avatar
        LeandroDeodato
        Icon for Resolver II rankResolver II
        2 years ago

        grow up in your variance field, your print show 0,0%, but i think its 0,0001% or any thing like this, for this dont be filtered.

        try use ROUND in this filed to test.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi All,

    Firstly,  LeandroDeodato  thanks for the solution!

    And k3rz0rg according to my understanding,  I want to share another method.

    When you have a problem using a filter to exclude data with a variance of 0, first make sure we are calculating the variance correctly,

    and then we can think about it differently by separating rows with a variance of 0 and rows with a variance of not 0 with an if condition, which might also solve your problem.

     

     

    Measure = (IF(SUM('Table'[Variance])=0,"zero", "not zero")
)

     

     

     

     

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi k3rz0rg ,

    Regarding the issue we discussed earlier, I wonder if this has been resolved now? It's been a long time since I've heard back from you and I'm not sure if all is well. Please feel free to let me know if there is anything else I can do to help.

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.