TRUNC

How to Use the Power BI DAX function TRUNC

Syntax of the TRUNC Function

The syntax of the TRUNC function in Power BI is as follows:


TRUNC(❰number❱, [❰num_digits❱])


- `❰number❱` is the decimal number that you want to round down.

- `[❰num_digits❱]` is an optional argument that specifies the number of decimal places that you want to round down to. If this argument is omitted, the number is rounded down to zero decimal places.

Examples of Using the TRUNC Function

Example 1: Rounding Down to Zero Decimal Places

Suppose you have a table in Power BI that contains a column of decimal values. You want to create a new column that rounds down each value to zero decimal places. Here's how you can achieve this using the TRUNC function:


New Column = TRUNC(‘Table'[Decimal Column])


This formula creates a new column in the table called 'New Column', which contains the truncated values of the original column. If the original column contains the value 3.14159, for example, the new column will contain the value 3.

Example 2: Rounding Down to a Specific Number of Decimal Places

Suppose you have a table in Power BI that contains a column of decimal values. You want to create a new column that rounds down each value to two decimal places. Here's how you can achieve this using the TRUNC function:


New Column = TRUNC(‘Table'[Decimal Column],2)


This formula creates a new column in the table called 'New Column', which contains the truncated values of the original column rounded down to two decimal places. If the original column contains the value 3.14159, for example, the new column will contain the value 3.14.

Example 3: Using TRUNC with Other DAX Functions

The TRUNC function can also be used in combination with other DAX functions to create more complex calculations. For example, suppose you have a table in Power BI that contains a column of sales data and a column of cost data. You want to calculate the profit margin for each row in the table, rounded down to two decimal places. Here's how you can achieve this using the TRUNC and DIVIDE functions:


Profit Margin = TRUNC(DIVIDE((‘Table'[Sales]), (‘Table'[Cost])),2)


This formula creates a new column in the table called 'Profit Margin', which contains the profit margin for each row in the table. The profit margin is calculated by dividing the sales value by the cost value, and then truncating the result to two decimal places. If the sales value is 100 and the cost value is 50, for example, the profit margin will be 2.00.

The TRUNC function is a useful DAX function in Power BI that allows you to round down decimal numbers to a specific number of decimal places. In this article, we've shown you how to use the TRUNC function in different scenarios, and provided examples of how it can be used in combination with other DAX functions to create more complex calculations. By mastering the TRUNC function, you can improve your data analysis skills and create more accurate and useful reports in Power BI.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

11-13 November 2024

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)