Forum Discussion

aiplbonn's avatar
aiplbonn
Regular Visitor
3 years ago

The calculation always returns a value without commas

Hey,

I'm stuck on a fairly simple point.
I have an import value in a field. This is usually in euro cents, but can also be in a completely different currency.
To convert this foreign currency into euros, I have a table with currency rates. I also let this currency rate be filled dynamically with the help of a lookup command.
Now I have an amount in cents in column A and the conversion rate in column B. The result of this calculation should now appear in C.
So A*B .

So far so good.

Unfortunately, PowerBi always delivers a result without , . So the calculation becomes

 

6250 * 0.0887 =5543750 and not 554,375.


No matter what I do, format as currency etc., it is never correct.

Even from 2142 Eurocent the calculation makes 2142 € and not 21,42€.

What am I doing wrong?

6 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    The type of the column is text, you need to change it to decimal number or fixed decimal number

  • aiplbonn's avatar
    aiplbonn
    Regular Visitor
    3 years ago

    Unfortunately, that changes nothing.

    If I select Fix decimal or only decimal as the data type, the result is 5543750.

    If I select Decimal for Format, the result is 5543750,00.

     

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      looks like the conversion rate is also a text field, not sure if an implicit cast is messing things up.

      • aiplbonn's avatar
        aiplbonn
        Regular Visitor
        3 years ago

        I found the error, the exchange rate already did not indicate that it was a decimal number.

        But now I have another problem. When I search for the exchange rate, I do it like this

         

        LOOKUPVALUE(exchange rate[currencytoEuro],exchange rate[currency_id],VoucherData[currency_id])

         

        If no matching exchange rate can be found, a 1 should be inserted.
        Previously I did it like this

         

        LOOKUPVALUE(exchange rate[currencyToEuro],exchange rate[currency_id],VoucherData[currency_id], "1")

         

        But now PowerBi is complaining because the 1 is written as text and not as a number.

         

        Expressions that result in the variant data type cannot be used to define calculated columns.

         

        How do I ensure that a 1 is inserted when the exchange rate is not found?