HashNotes

Power BI DAX: CALCULATE, AND, FILTER, DISTINCT

Mohamad Mahmood's photo
Mohamad Mahmood
·

2 min read

Transaction Size =
VAR SalesTotal = 'Sales'[Sales]
VAR FilterSegment =
    CALCULATE (
        'Transaction Size',
        AND (
            'Transaction Size'[Min] <= SalesTotal,
            'Transaction Size'[Max] >= SalesTotal
        )
    )
VAR Result =
    FILTER ( DISTINCT ( 'Transaction Size'[Transaction Size] ), FilterSegment )
RETURN
    Result

Brief explanation

In the context of the DAX expression above, "Transaction Size" refers to a table or column that categorizes sales transactions based on their total sales amount.

The DAX expression categorizes each sale into a transaction size segment based on the total sales amount. It checks which segment the SalesTotal falls into by comparing it against the Min and Max values in the 'Transaction Size' table and returns the corresponding transaction size.

Detailed breakdown

Explanation

  1. Transaction Size =: This is the name of the measure that is being created.

  2. VAR SalesTotal = 'Sales'[Sales]:

• VAR is used to define a variable.

• SalesTotal is the name of the variable.

• 'Sales'[Sales] refers to the total sales amount from the 'Sales' table.

  1. VAR FilterSegment = CALCULATE(...):

• FilterSegment is another variable.

• CALCULATE modifies the context of the data.

• Inside CALCULATE, it filters the 'Transaction Size' table to include only rows where the sales amount (SalesTotal) falls between the Min and Max values.

  1. VAR Result = FILTER(...):

• Result is the final variable.

• FILTER further refines the 'Transaction Size' table based on the FilterSegment condition.

  1. RETURN Result:

• This returns the filtered 'Transaction Size' values, which represent the category into which the sales transaction falls.

Practical Example

Imagine you have a 'Transaction Size' table with the following structure:

Transaction SizeMinMax
Small0100
Medium101500
Large5011000

If a sales transaction has a total sales amount of 150, the DAX expression will categorize it as "Medium" because 150 falls between 101 and 500.

Why It's Useful ?

Categorizing transactions by size can help you:

• Analyze Sales Performance: Understand which transaction sizes contribute most to your revenue.

• Customer Segmentation: Identify and target different customer segments based on their purchasing behavior.

• Trend Analysis: Spot trends in transaction sizes over time, helping with inventory and marketing strategies.

How to determine the ranges for each of the categories?

refer https://hashnotes.hashnode.dev/determining-transaction-size-thresholds-methods-and-best-practices

PowerBI