If you have ever used Excel formulas, you probably know how to use the dollar sign ($) to create fixed or absolute references. For example, if you want to refer to cell A1 in a formula and prevent it from changing when you copy or fill the formula to other cells, you can use $A$1 as the reference.
But what if you want to do the same thing in Power BI? How can you create fixed references in Power BI that don’t change when you apply filters, slicers, or other transformations?
In this article, I will show you two ways to create fixed references in Power BI: using the FIXED function and using Power Query Editor. By the end of this article, you will be able to use fixed references in Power BI like a pro without the help of any power bi consulting company.
Using the FIXED Function
The FIXED function is a DAX function that rounds a number to the specified number of decimals and returns the result as text. You can also specify whether to display commas or not in the returned text.
The syntax of the FIXED function is:
FIXED(<number>, <decimals>, <no_commas>)
- <number> is the number you want to round and convert to a text or a column containing a number.
- <decimals> (optional) is the number of digits to the right of the decimal point; if omitted, 2.
- <no_commas> (optional) is a logical value: if 1, do not display commas in the returned text; if 0 or omitted, display commas in the returned text.
For example, if you have a column called Sales Amount in your data model, and you want to create a measure that displays the total sales amount rounded to two decimals and with commas, you can use this formula:
Total Sales = FIXED(SUM([Sales Amount]), 2, 0)
This will return something like “1,234.56” as text.
But how does this help us create fixed references in Power BI?
The trick is to use a constant value instead of a column as the <number> argument. For example, if you want to create a measure that always returns 100 as text, regardless of any filters or slicers applied, you can use this formula:
Fixed Reference = FIXED(100, 0, 0)
This will return “100” as text in any context.
You can also use variables to store and use constant values in your formulas. For example, if you want to create a measure that calculates the percentage of sales amount over a fixed target of 10,000, you can use this formula:
Sales % of Target =
VAR Target = 10000
RETURN
DIVIDE(SUM([Sales Amount]), Target)
This will return a decimal value between 0 and 1 that represents the percentage of sales amount over the target.
Note that the FIXED function returns text values, not numbers. This means that you cannot perform arithmetic operations on them directly. You will need to convert them back to numbers using the VALUE function or other methods.
Using Power Query Editor
Another way to create fixed references in Power BI is to use Power Query Editor. Power Query Editor is a tool that lets you connect to various data sources, transform and shape your data, and load it into Power BI Desktop.
One of the features of Power Query Editor is that you can reference other queries in your data model. This means that you can use the steps from one query in another query without duplicating them.
For example, suppose you have a query called Sales those sources data from a web service and has some transformations applied to it. You can create another query called Fixed Reference that references the Sales query and returns a fixed value of 100.
To do this, follow these steps:
- In Power BI Desktop, go to Home > Transform Data to open Power Query Editor.
- In Power Query Editor, right-click on the Sales query and select Reference.
- A new query called Sales (2) will be created. Rename it as Fixed Reference.
- In the Fixed Reference query, go to Home > Advanced Editor.
- In the Advanced Editor window, replace the last line of code with this:
- In 100, Click Done.
- In the Fixed Reference query, go to Home > Close & Apply.
This will create a new table in your data model called Fixed Reference, with one column and one row containing the value 100.
You can now use this table as a fixed reference in your DAX formulas. For example, if you want to create a measure that calculates the percentage of sales amount over the fixed reference value, you can use this formula:
Sales % of Fixed Reference =
DIVIDE(SUM([Sales Amount]), Fixed Reference[Column1])
This will return a decimal value between 0 and 1 that represents the percentage of sales amount over the fixed reference value.
Note that using Power Query Editor to create fixed references can improve your data refresh time and reduce the impact on your data sources. This is because Power Query Editor will only retrieve the data from the source once and reuse it for the referenced queries.
Final Words
Fixed references can be useful when you want to compare your data to a constant value, such as a target, a benchmark, or a baseline. They can also help you create more dynamic and flexible reports that can adapt to different scenarios and contexts. If you want to learn more about Power BI and how to use it for your data analysis and visualization needs, you can contact a power bi consulting company that can help you with your projects. They can provide you with expert guidance, training, and support to help you get the most out of Power BI.
