How do you stop Power Bi Desktop rounding decimal numbers to a whole number?

[JPRogers]

I have created a new column which uses the Switch function to negate a value under certain conditions. 

INVENTORY_VALUE = SWITCH(TRX_DATA[RMS_TRANSACTION_CODE],"1",(TRX_DATA[RETAIL_VALUE]*-1.00),"30",TRX_DATA[RETAIL_VALUE],"16",(TRX_DATA[RETAIL_VALUE]*-1.00),0.00)

The switch statement is working well, but the result has been rounded to the nearest whole number and I would like to see the decimal places.

The [Retail Value] has a data type of decimal number and a format of decimal number with two decimal places and I have used the constants with two decimal places in the formula.

The result column has a data type of decimal numebr and a format of decimal number with two decimalplaces. The visualisation shows the two decimal places, but all set to zero, in that the retail value that is 22.50 which should be -22.50 is displayed as -23.00

I dont understand what is happening and how I ge resolve the issue.

[Sumanth_23]

hi @JPRogers - Can you please confirm if INVENTORY_VALUE is a DAX formula or a calculated column? 

It looks like the report visualization is setup to display 2 decimals but the measure itself maybe rounding off the result. Can you please check and confirm that the measure is setup as also setup as a Decimal number

Another point to note is that the visual level formatting overrides the measure level formatting. 

Also if possible can you share a sample of the report in question post removing any sensitive data. 

Hope this helps! 

[JPRogers]

Hi Sumanth,

Tahnk you for getting n touch, I am fairly new to PBI so forgive me if I havent answered your question fully.

Below is a screen shot of the screen showing the new column and the formula.

INVENTORY_VALUE is a new column added to the table. It is a calculated column. The data is from a Snowflake Database table and I am using Direct Query. The formula that defines the column negates the value in another column in the table from Snowflake under certain conditions.

The Snowflake column is RETAIL_VALUE, if the RMS Code is of a certain value, I want to negate value in the RETAIL_VALUE column and the result to be held in the INVENTROY_VALUE new column. New column is not in the databse but calculated in PBI.

Both the column from the database and the calculated column in PBI are Decimal Numbers and have two decimal places.

The column determining if the value should be negated or not, is the RMS_CODE, also from Snowflake, and is a text column.

Below is a screen shot of the dashboard, showing the attributes of the INVENTORY_VALUE column.

1. showing the column added to the table.

2, showing the Data Type as Decimla Number

3. showing the name of the column, INVENTORY_VALUE

4. showing the format, as Decimal Number with 2 Decimla Places

5, showing that I want to Sum it in the visualisation.

I cannot show the data in the visialisation, as its sensitive data, I'm sure you will understand, however, it shows as -23.00

[Sumanth_23]

hi @JPRogers - I was able to create a sample report and replicate the steps you are performing and able to create the calculated column as expected - I just keyed in some dummy data for the scenario you had explained

And the format of the calculated column seems to be carrying through to the visuals as expected. Please see screenshots below: 

Also attaching the .pbix file for reference. Hope this helps! 

[JPRogers]

Thank you Sumanth,

your solution and mine look identical as do the attributes of the columns, but I dont know why you can get the decimal places and mine rounds up.

Thanks for helping.

regards

John

[Sumanth_23]

hi @JPRogers - I am not sure if there is a way you can setup a local/offline version of the report and share a pbix file without sensitive data for my review - but if the solution is similar to what I have setup I am not sure what the issue could be. 

But definitely it looks like the value is getting rounded up before getting to the visual and hence you see the decimal places in the visual but its rounded up. 

Hope that helps!  

[AllisonKennedy]

@JPRogers  Can you upload a sample report demonstrating the issue and share via OneDrive please?

What's the data type of [RMS_TRANSACTION_CODE], not sure but this may be part of the issue?

[JPRogers]

Hi Alison,

Thank you for your reply, the column RMS_TRANSACTION_CODE is a text column. Do you think that it is influencing the result adversely? How do I get round that?