Decimals are not getting correctly in the report

[BhavyaM]

please help

Getting in Metrix table like below 

Account  Customer    Product 1      Product 2
A            A1                 614547.00000      614547.00001
A            B1                           0.00000      0.00000

But actual values are 
Account  Customer    Product 1      Product 2
A            A1                 614547     614547.00001
A            B1                           0               0


How to acheive like acutual values in report

[danextian]

Hi @BhavyaM 

Power BI automatically optimizes data types and remove trailing zeros when loading numeric fields so 0.0000 will be loaded as 0. 0.00001 though will be loaded as such unless rounded down in the query editor. You will need to get the number of decimal places in the query editor prior to changing the value to type number. You will also need to add index column to be able to show each record individually.

Dynamic Format String = 
"#,0." & REPT ( "0", SELECTEDVALUE ( 'Table'[Count of Decimals] ) )

The value column will need to be aggregated in a measure and a dynamic format string to be applied to it.

Please see the attached sample pbix.

[BhavyaM]

I dont want to do any Rounup or any query. What ever the actual values coming from the source, exactly i have to show in the report without doing any conversion. 

Actual values are the one which recieved from Source data

but when i try to add that columns into the report it is getting this converted by default to 2 decimals 614547.00 then i have given 5 decimals staticaly in the modeling menu. but this is not the expected case.

[DataNinja777]

Hi @BhavyaM ,

You can solve this issue by creating a dynamic formatting DAX measure that adjusts the decimal display based on whether the value contains nonzero decimal digits. The goal is to show whole numbers when there are no decimal values and retain decimal places when necessary.

To achieve this, define a DAX measure as follows:

Formatted_Value = 
VAR RawValue = SELECTEDVALUE('Table'[YourColumn])
RETURN 
    IF(
        RawValue = INT(RawValue), 
        FORMAT(RawValue, "0"), 
        FORMAT(RawValue, "#,##0.00000")
    )

This measure checks whether the value is a whole number using RawValue = INT(RawValue). If true, it formats the value without decimal places using "0". Otherwise, it retains up to five decimal places using "#,##0.00000". Apply this measure in your Matrix visual instead of the original column, ensuring each product column is represented by a corresponding measure. This will result in correct formatting where integers appear without decimals, while values with nonzero decimal fractions retain their original precision.

Best regards,