Power Pivot Principles: The A to Z of DAX Functions – FIXED
19 December 2023
In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at FIXED.
The FIXED function
The FIXED function is one of the text functions which returns the text value after rounding a number to the designated number of decimals. You may also specify whether the result should be returned with or without commas. It has the following syntax:
FIXED(number, decimals, no_commas)
- number: this argument is required and represents the number we wish to round and convert to text, or a column containing a number
- decimals: this is optional. It is the number of digits to the right of the decimal point and the default value here is two [2] if this argument is omitted
- no_commas: this is optional. It is a logical value. If we put one [1], it will not display commas in the return text and will if we put zero [0] in this argument. If this argument is omitted, it will return text including commas.
It should be noted that:
- if the value for decimals argument is negative, the number will be rounded to the left of the decimal point
- this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Let’s consider the following example where we have the following TB_Sales table:
As we can see here, the SalesAmount column is displayed to four [4] decimal places and the number is right-aligned here. We will write the following FIXED_Example measure:
FIXED_Example = FIXED(SUM(TB_Sales[SalesAmount]))
After we drag the OrderDate and the above measure in, you may be able to display something similar to the following:
In this picture, the OrderDate is identical to the source data and the SalesAmount here is left-aligned, with rounding to two [2] decimal places.
Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
