Forum Discussion

adilk's avatar
adilk
Helper I
9 years ago

Inumber not working in calculated column.

Hi,

I have a particular column [Search Impr. share] that has three possible values:

<10%

--

x%

If the column has x%, I want to be able to find out the eligible impressions. This would be created by dividing [actual impressions] column by [Search impr. share.]. I would create a calcuated field for this once I know which rows to use.

 

I tried creating a calculated column using ISNumber but I get all results as "No"

Eligible Impession = if(isnumber('Weekly Search'[Search Impr. share]),"Yes","No")

 

If I try to extract the first character of the [Search Impr. share] and multiply it by 1 (to convert it to a number), PBI doesn't accept it as the column can have both string and numeric values.

 

can anyone help with this, please.

Thanks.

 

 

Need Help

3 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi adilk,

    In your resource table, if the x% follow certain roles in the [Search Impr. share] field? If it does, you can split the column using Split function in Edit Query, then you can use the sun-column to discriminate.

    For example I have the following sample data.

     



    result = IF(Table8[Test.2]="X%","Yes","No")

     
    Click the table->Edit Query->Select Split columns->By Number Column of Characters , you will get the following window, you can set your roles according to your requriement.



    Click close&aplly, you will get the new table, please create calculated column using the formula, and you will get the expected result.

    result = IF(Table8[Test.2]="X%","Yes","No")




    If this is not what you want, please post the sample data for further analysis.

    Best Regards,
    Angelia

    • adilk's avatar
      adilk
      Helper I
      9 years ago

      Hi v-huizhn-msft

      Thanks for replying here. By X%, I meant that that the value could be a % value.

      So, the three types of possible values in this column are:

      --, <10% or 5%,10% (any actual % number).

      How would the solution change based on this input?

      -adil

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi adilk,

        You can use the following formula. Please add mutiple If.. Else condition, until all Table[column] values are included.

        result = IF(Table[column]<x%,"Yes",IF(Table[column]<Y%,"Yes","No"))


         Best Regards,
        Angelia