Introduction to MAXX Function in DAX

Setup Instructions

1. Open Power BI Desktop
2. Import your dataset into Power BI

Practical Implementation

Create a New Measure Using MAXX

1. Select your table
2. Create a New Measure

Here is an example of how to use the MAXX function to find the maximum sales amount in a table:

MaxSalesAmount = MAXX(Sales, Sales[Amount])

Create a New Column Using MAXX

1. Select your table
2. Create a New Column

Here is an example of how to use the MAXX function to calculate the maximum value within a related table for each row:

MaxSalesPerProduct = MAXX(RELATEDTABLE(Sales), Sales[Amount])

Using MAXX with FILTER

You can use MAXX to find the maximum value with a specific condition by combining it with the FILTER function:

MaxSalesIn2023 = MAXX(FILTER(Sales, Sales[Year] = 2023), Sales[Amount])

Practical Examples

• Maximum Sales by Product Category:

  MaxSalesByCategory = MAXX(
      SUMMARIZE(Sales, Sales[ProductCategory], "TotalSales", SUM(Sales[Amount])),
      [TotalSales]
  )

• Maximum Profit Margin:

  MaxProfitMargin = MAXX(
      Sales,
      DIVIDE(Sales[Profit], Sales[Amount])
  )

Conclusion

You've successfully used the MAXX function in DAX to retrieve maximum values based on different conditions and scenarios. Apply these implementations to your datasets as needed.

Simple Applications of MAXX with Examples (Unit #2)

Example 1: Finding the Maximum Sales Amount

MaxSalesAmount = MAXX(Sales, Sales[Amount])

Example 2: Maximum Revenue per Product Category

MaxRevenuePerCategory = MAXX(
    SUMMARIZE(
        Sales, 
        Product[Category], 
        "Revenue", SUM(Sales[Amount])
    ), 
    [Revenue]
)

Example 3: Highest Discount Given

MaxDiscount = MAXX(Sales, Sales[Discount])

Example 4: Maximum Profit Margin

MaxProfitMargin = MAXX(
    Sales, 
    (Sales[Revenue] - Sales[Cost]) / Sales[Revenue]
)

Example 5: Maximum Stock Price per Year

MaxStockPricePerYear = MAXX(
    SUMMARIZE(
        StockPrices, 
        StockPrices[Year], 
        "MaxPrice", MAX(StockPrices[Price])
    ), 
    [MaxPrice]
)

Example 6: Maximum Order Quantity by Customer

MaxOrderQuantityByCustomer = MAXX(
    SUMMARIZE(
        Sales, 
        Sales[CustomerID], 
        "TotalOrderQuantity", SUM(Sales[Quantity])
    ), 
    [TotalOrderQuantity]
)

Advanced Usage Scenarios with MAXX in DAX

Applying MAXX for Complex Calculations

Scenario 1: Calculate Maximum Sales Per Category (including discount)

MaxSalesPerCategory = 
ADDCOLUMNS(
    VALUES(Sales[Category]),
    "MaxSales",
    MAXX(
        FILTER(Sales, Sales[Category] = EARLIER(Sales[Category])),
        Sales[Quantity] * (Sales[Price] - Sales[Discount])
    )
)

Scenario 2: Identify Maximum Profit Margin Per Region

MaxProfitMarginPerRegion = 
ADDCOLUMNS(
    VALUES(Sales[Region]),
    "MaxProfitMargin",
    MAXX(
        FILTER(Sales, Sales[Region] = EARLIER(Sales[Region])),
        (Sales[Revenue] - Sales[Cost]) / Sales[Revenue]
    )
)

Scenario 3: Maximum Duration Per Project with Conditions

MaxDurationPerProject =
ADDColumns(
    VALUES(Projects[ProjectID]),
    "MaxDuration",
    MAXX(
        FILTER(
            Projects,
            Projects[ProjectID] = EARLIER(Projects[ProjectID]) 
                && Projects[Status] = "Completed"
        ),
        Projects[Duration]
    )
)

Scenario 4: Calculate Maximum Sales Date for Each Product

MaxSalesDatePerProduct = 
ADDColumns(
    VALUES(Sales[ProductID]),
    "MaxSalesDate",
    MAXX(
        FILTER(Sales, Sales[ProductID] = EARLIER(Sales[ProductID])),
        Sales[SaleDate]
    )
)

Scenario 5: Maximum Customer Spend Per Segment with Date Constraint

MaxCustomerSpendPerSegment =
ADDCOLUMNS(
    VALUES(Customers[Segment]),
    "MaxCustomerSpend",
    MAXX(
        FILTER(
            Sales,
            Sales[Segment] = EARLIER(Customers[Segment]) &&
            Sales[SaleDate] >= DATE(2022,1,1)
        ),
        Sales[TotalSpend]
    )
)

These advanced DAX implementations using the MAXX function provide practical usage scenarios that can handle a variety of real-world business requirements, offering efficient and effective data analysis insights.

Practical Implementation of Combining MAXX with Other DAX Functions

Scenario: Calculate the Maximum Sales for Each Region

1. Dataset: Assume you have a Sales table with columns Region, Product, and SalesAmount.

2. Objective: Calculate the highest sales amount for each region and return the corresponding product's name.

Measure: Max Sales Amount by Region

MaxSalesByRegion = 
MAXX(
    VALUES(Sales[Region]),
    CALCULATE(MAX(Sales[SalesAmount]))
)

Measure: Product with Max Sales by Region

ProductWithMaxSalesByRegion = 
VAR MaxSalesAmount = 
    MAXX(
        VALUES(Sales[Region]),
        CALCULATE(MAX(Sales[SalesAmount]))
    )
RETURN 
    MAXX(
        FILTER(Sales, Sales[SalesAmount] = MaxSalesAmount),
        Sales[Product]
    )

Measure: Summarize All Results

SummaryTable = 
SUMMARIZE(
    Sales,
    Sales[Region],
    "Max Sales Amount", [MaxSalesByRegion],
    "Top Product", [ProductWithMaxSalesByRegion]
)

Usage in a Table Visualization

• Display SummaryTable in a Power BI table visualization to show each region with its maximum sales amount and corresponding top product.

Example Output

This practical implementation enables you to calculate and analyze the highest sales by region and identify the top-performing products within each region using the MAXX function in DAX combined with other relevant DAX functions.

Practical Data Analysis Using MAXX

#5 Deep Dive into the MAXX Function in DAX and Its Practical Applications

MAXX Function Basic Syntax

MAXX(, )
Practical Applications
1. Find Maximum Sales Amount
Retrieve the highest sales amount from a sales table.
MaxSalesAmount = MAXX(Sales, Sales[Amount])
2. Maximum Sales by Each Product
Calculate the maximum sales amount per product.
MaxSalesPerProduct = 
SUMMARIZE(
    Sales, 
    Sales[ProductID], 
    "MaxSales", MAXX(FILTER(Sales, Sales[ProductID] = EARLIER(Sales[ProductID])), Sales[Amount])
)
3. Maximum Revenue for Each Year
Determine the maximum revenue for each year in the sales data.
MaxYearlyRevenue = 
SUMMARIZE(
    Sales, 
    Sales[Year], 
    "MaxRevenue", MAXX(FILTER(Sales, Sales[Year] = EARLIER(Sales[Year])), Sales[Amount])
)
4. Highest Average Sales Per Customer
Find the customer with the highest average sales.
HighestAvgSalesCustomer = 
CALCULATE(
    MAXX(
        VALUES(Sales[CustomerID]), 
        CALCULATE(AVERAGEX(FILTER(Sales, Sales[CustomerID] = EARLIER(Sales[CustomerID])), Sales[Amount]))
    )
)
5. Max Sales Amount in Specific Category
Filter your analysis to a specific product category to find the maximum sales amount.
MaxSalesInCategory = 
CALCULATE(
    MAXX(Sales, Sales[Amount]), 
    Sales[ProductCategory] = "Electronics"
)
Conclusion
These practical DAX queries using the MAXX function showcase how to derive meaningful insights by identifying maximum values in various contexts within your dataset. Apply these examples directly to your tables to enhance your data analysis capabilities.
6navigate_next
Deep Dive into the MAXX Function in DAX and its Practical Applications
Step-by-Step Project Implementation

Identify Target Dataset
SalesTable (OrderID, ProductID, SalesAmount, OrderDate)
Calculate Maximum Sales per Product
MaxSalesPerProduct =
ADDCOLUMNS(
    VALUES(SalesTable[ProductID]),
    "MaxSales", MAXX(
        FILTER(SalesTable, SalesTable[ProductID] = EARLIER(SalesTable[ProductID])),
        SalesTable[SalesAmount]
    )
)
Find the Overall Maximum Sales Value
OverallMaxSales = MAXX(SalesTable, SalesTable[SalesAmount])
Determine Maximum Sales for Each Month
MaxSalesPerMonth =
SUMMARIZE(
    SalesTable,
    SalesTable[OrderDate].[Month],
    "MaxMonthlySales", MAXX(
        FILTER(SalesTable, SalesTable[OrderDate].[Month] = EARLIER(SalesTable[OrderDate].[Month])),
        SalesTable[SalesAmount]
    )
)
Calculate the Maximum Sales for Each Customer
CustomerMaxSales =
ADDCOLUMNS(
    VALUES(SalesTable[CustomerID]),
    "MaxSales", MAXX(
        FILTER(SalesTable, SalesTable[CustomerID] = EARLIER(SalesTable[CustomerID])),
        SalesTable[SalesAmount]
    )
)
Dynamic Calculation with User-Specific Filters
MaxSalesFiltered =
CALCULATE(
    MAXX(SalesTable, SalesTable[SalesAmount]),
    USERELATIONSHIP(SalesTable[UserID], UserTable[UserID]),
    FILTER(UserTable, UserTable[Region] = "PreferredRegion")
)
Integrate with a Visualization Tool
MaxSalesViz =
SUMMARIZE(
    SalesTable,
    SalesTable[ProductID],
    "MaxSalesViz", MAXX(
        SalesTable,
        SalesTable[SalesAmount]
    )
)
Logging and Verification

Ensure all calculations are accurate.
Verify with subset data.
Validate results with business logic.



End of Implementation
More Code Generators
 Apache Flink Code Generator Apache Pig Code Generator Azure Data Factory Code Generator C/C++ Code Generator CouchDB Code Generator DAX Code Generator Excel Code Generator Firebase Code Generator Google BigQuery Code Generator Google Sheets Code Generator GraphQL Code Generator Hive Code Generator Java Code Generator JavaScript Code Generator Julia Code Generator Lua Code Generator M (Power Query) Code Generator MATLAB Code Generator MongoDB Code Generator Oracle Code Generator PostgreSQL Code Generator Power BI Code Generator Python Code Generator R Code Generator Redis Code Generator Regex Code Generator Ruby Code Generator SAS Code Generator Scala Code Generator Shell Code Generator SPSS Code Generator SQL Code Generator SQLite Code Generator Stata Code Generator Tableau Code Generator VBA Code Generator