Forum Discussion

CW_85's avatar
CW_85
New Member
3 years ago
Solved

IF "text" than "formula"

Hello,

 

I've been using PowerBI for a while and now I want to add a formula to a column that shows the percentage of one amount (red lines) against the total amount (green lines). For this I entered this formula: Column = IF(ISTEXT(FIND("RFC",Facturatieoverzicht[Omschrijving])),Facturatieoverzicht[Bedrag]/SUM(Facturatieoverzicht[Bedrag])*100," ")

 

 

The invoice text is described in the Description column. Everything that contains the text "RFC", he must apply the formula to, all other fields may remain empty. However, the above formula gives me the following error and I don't know how to solve it: You cannot use an expression that returns the Variant data type to define calculated columns.

 

I don't know what to do next, can somebody help me?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi CW_85 ,

     

    According to your statement, I know that you are creating a calculated column. As far as I know, there should be only one data type in Power BI Column.

    Facturatieoverzicht[Bedrag]/SUM(Facturatieoverzicht[Bedrag])*100 will return number type value, however "" will return text type value. 

    And I suggest you to try CONTAINSSTRING() function to check whether the value contains "RFC".

    Calculated column:

    Column =
IF (
    CONTAINSSTRING ( Facturatieoverzicht[Omschrijving], "RFC" ),
    Facturatieoverzicht[Bedrag] / SUM ( Facturatieoverzicht[Bedrag] ) * 100,
    BLANK ()
)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

2 Replies