Explanation of Average Sales Bins DAX Code

Code Overview

The provided Data Analysis Expressions (DAX) code calculates a value and then categorizes each row in the 'Sales' table based on whether its revenue matches the average revenue. If it matches, it labels it as "Average Revenue"; otherwise, it returns the revenue or "All".

Average Sales Bins = 
VAR Average_Revenue = AVERAGEX('Sales', 'Sales'[Revenue])
RETURN
    IF(
        SELECTEDVALUE ('Sales'[Revenue]) = Average_Revenue,
        "Average Revenue",
        SELECTEDVALUE ('Sales'[Revenue], "All")
    )

Explanation of Key Components

1. VAR Average_Revenue

• Purpose: This line defines a variable Average_Revenue which stores the average revenue of all entries in the 'Sales' table.
• Function Used: AVERAGEX is an iterator that evaluates an expression for each row of a table and returns the average of all those values.

VAR Average_Revenue = AVERAGEX('Sales', 'Sales'[Revenue])

2. RETURN

• Purpose: RETURN indicates the expression to be computed as the result of the variable and logic defined.

RETURN

3. IF Statement

• Purpose: This conditional logic determines the label for each revenue value.
• Function Used: SELECTEDVALUE returns the value when the context for the column is a single value; otherwise, returns alternate result or BLANK().

IF(
    SELECTEDVALUE ('Sales'[Revenue]) = Average_Revenue,
    "Average Revenue",
    SELECTEDVALUE ('Sales'[Revenue], "All")
)

Breakdown:

• Condition: Checks if the revenue of the current row is equal to the calculated Average_Revenue.
• True Result: If condition is true, it returns the string "Average Revenue".
• False Result: If condition is false, it returns the actual revenue of the current row or the string "All".

4. SELECTEDVALUE('Sales'[Revenue], "All")

• Fallback Value: The second argument is a fallback value "All" that will be returned if there's no single selected value in the current context.

SELECTEDVALUE ('Sales'[Revenue], "All")

Additional Examples

Example 1: Simple Average Calculation

If you are interested in just calculating the average of a column without labels:

Average Revenue Value = AVERAGEX('Sales', 'Sales'[Revenue])

Example 2: Categorizing Based on Average

Another variation might categorize values into bins such as "Above Average" or "Below Average":

Revenue Category = 
VAR Average_Revenue = AVERAGEX('Sales', 'Sales'[Revenue])
RETURN
    IF(
        SELECTEDVALUE('Sales'[Revenue]) > Average_Revenue,
        "Above Average",
        "Below Average"
    )

Conclusion

The provided DAX code is a straightforward way to dynamically create categories based on whether a row's revenue is equal to the average revenue of the entire dataset. This technique exemplifies how DAX can be used to calculate values and incorporate conditional logic within calculated columns or measures. Further explorations and enhancements of DAX functionalities can be explored on the Enterprise DNA platform.