SIGN

How to Use the Power BI DAX function SIGN

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:

SIGN(number)

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:

= SIGN(Sales)

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:

= DIVIDE(

COUNTX(

FILTER(Sales, SIGN(Sales) = 1),

Sales

),

COUNT(Sales)

)

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(

SUM(Sales),

FILTER(Sales, SIGN(Sales) = 1)

)

Negative Sales = CALCULATE(

SUM(Sales),

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.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)