Format numeric precision based on table value

[bdherh]

I have a PowerBI table in a dashboard that has a number of numeric columns.  I have a fixed 2 decimals for each of them.  But a request was made to have this based on a precision value in the table.  How (if any) can I use this value to format the numberic value

For example:

Table:  MyValues

Column Name     Type      

colid                     int         

Value1                 Float

Value1Precision   int        

Value2                 Float

Value2Precision   int

Values

colid          Value1         Value1Precision        Value2            Value2Precision

1                33.557                 2                       77.54                      3

2                44.22                   2                       674.333                  3

Needed PowerBi Table Displayed

Id                 Value1         Value2          

1                   33.58          77.540

2                   44.22          674.333

Thank you

[v-echaithra]

Hi @bdherh ,

Thank you @bdherh for addressing the thread. The solution you provided is almost correct, with just a minor correction, the function REPLICATE should be replaced with REPT.

Formatted Value1 =

VAR Precision = SELECTEDVALUE('Table'[Value1Precision])

VAR FormatString = "0." & REPT("0", Precision)

RETURN

FORMAT(SELECTEDVALUE('Table'[Value1]), FormatString)

Formatted Value2 =

VAR Precision = SELECTEDVALUE('Table'[Value2Precision])

VAR FormatString = "0." & REPT("0", Precision)

RETURN

FORMAT(SELECTEDVALUE('Table'[Value2]), FormatString)

Please Check the attatched PBIX file, which works fine for the samples yoiu have provided.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,
Chaithra E.

[DataNinja777 · Accepted solution]

Hi @bdherh ,

You can achieve dynamic numeric precision in a Power BI table by creating a new measure or calculated column that uses DAX functions to format your numbers based on the corresponding precision value in your table. The most effective function for this task is FORMAT, which converts a number into a text string using a specified format. This approach allows you to construct the format string dynamically for each row.

The recommended method is to create a new measure for each numeric column you want to format. Measures are calculated on the fly and are generally more efficient for your Power BI model's performance and size compared to calculated columns. To format Value1, you would create a new measure using the following DAX expression. This code first determines the required precision and then builds a format string, like "0.00" for a precision of 2, before applying it to the value.

Formatted Value1 = 
VAR Precision = SELECTEDVALUE(MyValues[Value1Precision])
VAR FormatString = "0." & REPLICATE("0", Precision)
RETURN
FORMAT(SELECTEDVALUE(MyValues[Value1]), FormatString)

Similarly, you would create a second measure to handle the formatting for Value2 based on its respective precision column. The logic remains the same, only referencing the Value2 and Value2Precision columns instead.

Formatted Value2 = 
VAR Precision = SELECTEDVALUE(MyValues[Value2Precision])
VAR FormatString = "0." & REPLICATE("0", Precision)
RETURN
FORMAT(SELECTEDVALUE(MyValues[Value2]), FormatString)

After creating these measures, you would modify your table visual by removing the original Value1 and Value2 columns and adding the new Formatted Value1 and Formatted Value2 measures in their place. This will display the numbers formatted to the correct decimal place as specified row by row.

Alternatively, you could use calculated columns to achieve the same result. This approach adds the formatted data directly to your table in the data model. To do this, you would navigate to the Data view, select your table, and create a new column with a DAX formula like the one below for Value1.

Formatted Value1 Column = FORMAT([Value1], "0." & REPLICATE("0", [Value1Precision]))

It is important to understand that both of these methods convert the numeric data into a text data type. Consequently, the new formatted measures or columns are suitable only for display purposes. You cannot perform further mathematical operations, such as summing or averaging, directly on them within the visual. Any aggregations or calculations must still be performed on the original, unformatted numeric columns.

Best regards,

[Kedar_Pande]

@bdherh 

Use a measure like this for dynamic formatting:

Formatted Value1 = 
FORMAT ( 
ROUND ( MyValues[Value1], MyValues[Value1Precision] ), 
"0." & REPT("0", MyValues[Value1Precision])
)

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn