Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Help: formula find or search

Hello!!

Im new in PBI, I want someone could help me with this:

 

I have a column that calculates with numbers that have two decimals, I need (without considering the decimals) to take the last three digits and if these are zeros (the three) take the thirty largest ones, for example
Colum
LC
100100.45
2345000.00
34000.10
123000.00

I should return the 2nd, 3rd and 4th and those sort from highest to lowest and return the first 30 records.

Thank you very much for your help
Monica

  • ricardocamargos's avatar
    ricardocamargos
    8 years ago

    Hi Anonymous,

     

    Try this code creating a new column:

    Test = IF(RIGHT(INT(Table1[Column1]); 3) = "000"; 1; 0)

     

    After that you can filter the report or page by this new column.

     

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous

     

    I am trying to undeerstand, but sorry, couldnet get your question. Could you please explain what logic you want?

     

    Thanks

    Raj

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi!!!

      Thanks.

       

      This is the scenario:  I have a colum with number like:  12,345.89, 673,000.76, 8700.00, 34,000.00 etc. And I need only the records when the last three digits before the dot, are 0; for example from this list; I only should show 673,000.76 and 34,000.00 (this two numbers have three digits cero, before de dot.

       

      I dont know how to do that.

      • ricardocamargos's avatar
        ricardocamargos
        Continued Contributor
        8 years ago

        Hi Anonymous,

         

        Try this code creating a new column:

        Test = IF(RIGHT(INT(Table1[Column1]); 3) = "000"; 1; 0)

         

        After that you can filter the report or page by this new column.