IF "text" than "formula"

[CW_85]

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?

[v-rzhou-msft · Accepted solution]

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.

[lbendlin]

1. Why ISTEXT  ?

2. Are you sure you want this as a column?  Sounds more like a use case for a measure.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...