What is the SIGN function?
The SIGN function is a mathematical function that returns the sign of a number. If the number is positive, it returns 1; if the number is negative, it returns -1; and if the number is zero, it returns 0. The syntax for the SIGN function is as follows:
The "number" argument is the number for which you want to determine the sign.
How to use the SIGN function in Power BI
Using the SIGN function in Power BI is pretty straightforward. Let's take a look at a few examples.
Example 1: Determine the sign of a number
Suppose you have a column named "Sales" in your Power BI dataset, and you want to determine the sign of each value in that column. You can use the following DAX formula:
This formula will return 1 for positive values, -1 for negative values, and 0 for zero values.
Example 2: Calculate the percentage of positive numbers
Suppose you want to calculate the percentage of positive numbers in the "Sales" column. You can use the following DAX formula:
FILTER(Sales, SIGN(Sales) = 1),
Let's break this formula down. The FILTER function is used to filter the "Sales" column based on the condition that the sign of the value should be 1 (i.e., positive). The COUNTX function is used to count the number of values in the filtered column. Finally, the COUNT function is used to count the total number of values in the "Sales" column. The DIVIDE function is used to divide the number of positive values by the total number of values, giving us the percentage of positive values.
Example 3: Display positive and negative values separately
Suppose you want to display the positive and negative values in separate columns. You can use the following DAX formulas:
Positive Sales = CALCULATE(
FILTER(Sales, SIGN(Sales) = 1)
Negative Sales = CALCULATE(
FILTER(Sales, SIGN(Sales) = -1)
The CALCULATE function is used to calculate the sum of the "Sales" column based on the specified condition. The FILTER function is used to filter the "Sales" column based on the condition that the sign of the value should be 1 for positive values and -1 for negative values.
The SIGN function is a powerful tool in Power BI that can be used for a variety of purposes, from determining the sign of a number to calculating percentages and displaying positive and negative values separately. By using the examples provided in this article, you can start incorporating the SIGN function into your Power BI reports and gain valuable insights from your data.