topic Re: The calculation always returns a value without commas in DAX Commands and Tips

The calculation always returns a value without commas

aiplbonn — Wed, 18 Jan 2023 09:12:30 GMT

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?

Re: The calculation always returns a value without commas

johnt75 — Wed, 18 Jan 2023 09:47:57 GMT

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

Re: The calculation always returns a value without commas

aiplbonn — Wed, 18 Jan 2023 10:45:23 GMT

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.

Re: The calculation always returns a value without commas

johnt75 — Wed, 18 Jan 2023 10:49:12 GMT

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

Re: The calculation always returns a value without commas

aiplbonn — Wed, 18 Jan 2023 11:39:07 GMT

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?

Re: The calculation always returns a value without commas

johnt75 — Wed, 18 Jan 2023 11:42:11 GMT

just remove the " around the 1.

Re: The calculation always returns a value without commas

aiplbonn — Wed, 01 Feb 2023 14:41:34 GMT

Thank you all 🙂 This was the mistake