Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
JPRogers
Regular Visitor

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

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.
7 REPLIES 7
Sumanth_23
Memorable Member
Memorable Member

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. 

Sumanth_23_0-1619517506279.png

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

Hope this helps! 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



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.

 

JPRogers_0-1619535406076.png

 

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

 

 

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: 

 

Sumanth_23_1-1619556925092.png

 

Sumanth_23_2-1619557033540.png

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



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

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!  

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



AllisonKennedy
Super User
Super User

@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?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.