Sign In
Get Started

MAXX in Power BI – A Detailed Guide

by | Power BI

Table of Contents

Understanding the MAXX Function in DAX

Setup Instructions

Ensure you have access to Power BI or a similar environment that supports DAX.
Import your data into Power BI or relevant platform, which we will reference in the examples.

Practical Implementation

Example Dataset

Assume we have a dataset named Sales:

Columns: ProductID, SalesAmount, Month, Year

Step 1: Calculate the Maximum Sales Amount for Each Product

Create a new measure to find the maximum sales amount for each product:

Sales Now On Advertisement 
MaxSalesAmountPerProduct = 
MAXX(
    Sales,
    Sales[SalesAmount]
)

Step 2: Calculate the Maximum Sales Amount Over All Products

Create a measure to find the maximum sales amount across all products:

MaxSalesAmountOverall = 
MAXX(
    ALL(Sales),
    Sales[SalesAmount]
)

Step 3: Calculate the Maximum Sales Amount for Each Year

Create a new measure to find the maximum sales amount per year:

MaxSalesAmountPerYear = 
CALCULATE(
    MAXX(
        Sales,
        Sales[SalesAmount]
    ),
    VALUES(Sales[Year])
)

Step 4: Combine MAXX with Other Functions

Combine MAXX with another DAX function to find the maximum sales amount across different years but with additional conditions.

Example: Maximum Sales in a Specific Year

MaxSalesIn2020 = 
CALCULATE(
    MAXX(
        FILTER(
            Sales,
            Sales[Year] = 2020
        ),
        Sales[SalesAmount]
    )
)

Step 5: Visualize the Results

Create visualizations in Power BI using the measures to display the maximum sales amounts.

Table Visualization:
Add ProductID and MaxSalesAmountPerProduct.
Card Visualization:
Add MaxSalesAmountOverall to a card.
Line Chart:
Axis: Year
Values: MaxSalesAmountPerYear

Conclusion

Utilize the measures in your visuals to gain insights on maximum sales across different dimensions. The MAXX function is versatile and helps derive maximum values within specified contexts. Continue exploring combinations with other DAX functions for more complex analyses.

Practical Implementation of MAXX

Example 1: MAXX with a Simple Table

EVALUATE
ADDCOLUMNS (
    Sales,
    "Max Sales Amount", MAXX ( Sales, Sales[SalesAmount] )
)

Example 2: MAXX with FILTER

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        Sales,
        "Max Sales Amount by Product", MAXX ( FILTER ( Sales, Sales[ProductID] = 123 ), Sales[SalesAmount] )
    ),
    Sales[ProductID] = 123
)

Example 3: MAXX with GROUPBY

EVALUATE
GROUPBY (
    Sales,
    Sales[ProductID],
    "Max Sales By Product", MAXX ( CURRENTGROUP (), Sales[SalesAmount] )
)

Example 4: Using MAXX with SUMMARIZE

EVALUATE
ADD COLUMNS (
    SUMMARIZE (
        Sales,
        Sales[ProductID],
        Sales[Date],
        "Max Sales By Product and Date", MAXX ( SUMMARIZE ( Sales, Sales[SalesAmount] ), Sales[SalesAmount] )
    )
)

Example 5: MAXX within a Measure

Measure_MaxSalesAmount = 
MAXX ( 
    Sales, 
    Sales[SalesAmount] 
)

EVALUATE
    SUMMARIZE (
        Sales,
        Sales[ProductID],
        "Max Sales Amount", [Measure_MaxSalesAmount]
    )

Example 6: MAXX with a Date Range

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        Sales,
        "Max Sales Amount Date Range", MAXX ( FILTER ( Sales, Sales[Date] >= DATE(2022, 1, 1) && Sales[Date] <= DATE(2022, 12, 31) ), Sales[SalesAmount] )
    )
)

These practical examples demonstrate various ways to leverage the MAXX function in DAX for real-life data analysis tasks.

Advanced Examples of MAXX in DAX

Example 1: Using MAXX with FILTER for Date Calculations

-- Calculate the maximum sales amount for transactions made in the year 2022

MAX_SALES_2022 = 
MAXX(
    FILTER(
        Sales,
        Sales[TransactionDate] >= DATE(2022, 1, 1) && Sales[TransactionDate] <= DATE(2022, 12, 31)
    ),
    Sales[SalesAmount]
)

Example 2: Combining MAXX and RELATED for Customer Analysis

-- Find the maximum order quantity for each product considering only active customers

MAX_ORDER_ACTIVE_CUSTOMERS = 
MAXX(
    FILTER(
        Sales,
        RELATED(Customer[Status]) = "Active"
    ),
    Sales[OrderQuantity]
)

Example 3: Using MAXX with AVERAGEX to Identify Outliers

-- Calculate the maximum sales amount deviation from average sales amount per product

MAX_SALES_DEVIATION = 
MAXX(
    Product,
    ABS(
        CALCULATE(SUM(Sales[SalesAmount]), Sales[ProductID] = Product[ProductID]) - 
        AVERAGEX(
            Sales,
            Sales[SalesAmount]
        )
    )
)

Example 4: Combining MAXX and SUMX for Profit Calculation

-- Calculate the maximum profit considering sales amount with associated costs for different regions

MAX_PROFIT_BY_REGION = 
MAXX(
    SUMMARIZE(
        Sales, 
        Sales[Region], 
        "TotalProfit", SUMX(Sales, Sales[SalesAmount] - Sales[Cost])
    ),
    [TotalProfit]
)

Example 5: Using MAXX Within Dynamic On-the-Fly Calculations

-- Calculate the maximum number of units sold for on-the-fly created categories (e.g., tiered pricing)

MAX_UNITS_BY_TIER = 
MAXX(
    ADDCOLUMNS(
        Product,
        "PriceTier", IF(Product[Price] < 50, "Low", IF(Product[Price] < 100, "Medium", "High"))
    ),
    CALCULATE(SUM(Sales[UnitsSold]))
)

Conclusion

These examples demonstrate practical advanced usage of the MAXX function combined with various other DAX functions to achieve complex data analysis objectives. Apply these in real-world scenarios to drive insightful and actionable data outcomes.

Power BI Tools Advertisement

Part 4: Combining MAXX with Other Essential DAX Functions

Combining MAXX with CALCULATE

MaxSalesByCategory = 
CALCULATE(
    MAXX(
        Sales, 
        Sales[SalesAmount]
    ),
    ALLEXCEPT(Sales, Sales[Category])
)

Using MAXX with FILTER

TopHighValueOrders = 
MAXX(
    FILTER(
        Sales, 
        Sales[OrderValue] > 1000
    ),
    Sales[OrderValue]
)

Combining MAXX with SUMX

MaxTotalProfit =
MAXX(
    VALUES(Sales[Region]),
    SUMX(
        RELATEDTABLE(Sales),
        Sales[Quantity] * Sales[Profit]
    )
)

Using MAXX with EARLIER

MaxSalePerCustomer = 
MAXX(
    FILTER(
        Sales,
        Sales[CustomerID] = EARLIER(Sales[CustomerID])
    ),
    Sales[SalesAmount]
)

Combining MAXX with AVERAGEX

MaxAvgSalesPerProduct =
MAXX(
    VALUES(Sales[Product]),
    AVERAGEX(
        RELATEDTABLE(Sales),
        Sales[SalesAmount]
    )
)

Ensure you integrate these DAX formulas seamlessly within your existing data model and Power BI reports.

Part 5: Integrating MAXX Formulas into Data Models

Practical Implementation

Scenario 1: Find the Order with the Maximum Total Sales

Data Model:

  • Table Orders containing columns: OrderID, CustomerID, OrderDate, TotalSales

DAX Implementation:

MaxOrderSales = MAXX (
  Orders,
  Orders[TotalSales]
)

Scenario 2: Identify the Product with Maximum Revenue in Each Category

Data Model:

  • Table Products containing columns: ProductID, CategoryID, Revenue

DAX Implementation:

MaxProductRevenuePerCategory = 
  SUMMARIZE (
    Products, 
    Products[CategoryID], 
    "MaxRevenue", MAXX(
      FILTER(
        Products, 
        EARLIER ( Products[CategoryID] ) = Products[CategoryID]
      ), 
      Products[Revenue]
    )
  )

Scenario 3: Calculate the Maximum Discount Given

Data Model:

  • Table Sales containing columns: SaleID, CustomerID, Discount

DAX Implementation:

MaxDiscountGiven = MAXX (
  Sales,
  Sales[Discount]
)

Scenario 4: Maximum Sales by Employee

Data Model:

  • Table Employees containing columns: EmployeeID, EmployeeName
  • Table Sales containing columns: SaleID, EmployeeID, TotalSales

DAX Implementation:

MaxSalesByEmployee = 
  ADDCOLUMNS (
    Employees, 
    "MaxSales",
    MAXX (
      FILTER (
        Sales, 
        Sales[EmployeeID] = Employees[EmployeeID]
      ),
      Sales[TotalSales]
    )
  )

Scenario 5: Maximum Quantity Sold Per Product

Data Model:

  • Table Sales containing columns: SaleID, ProductID, Quantity

DAX Implementation:

MaxQuantityPerProduct = 
  ADDCOLUMNS (
    VALUES ( Sales[ProductID] ),
    "MaxQuantity",
    MAXX (
      FILTER (
        Sales,
        Sales[ProductID] = EARLIER ( Sales[ProductID] )
      ),
      Sales[Quantity]
    )
  )

Conclusion

The provided DAX code snippets are practical implementations of how the MAXX function can be applied to real-world scenarios within your data models. Simply integrate these formulas into your existing DAX scripts or use them accordingly within your analysis tools.

Project-Based Practice and Applications (Part 6)

Scenario: Top Performing Products per Category

Given a dataset Sales with columns ProductID, Category, SalesAmount, implement DAX measures to find the top performing product in each category based on total sales.

Step 1: Calculate Total Sales per Product

TotalSalesPerProduct = 
SUMX(
    VALUES(Sales[ProductID]),
    CALCULATE(SUM(Sales[SalesAmount]))
)

Step 2: Calculate Maximum Sales per Category

MaxSalesPerCategory = 
MAXX(
    SUMMARIZE(
        Sales, 
        Sales[Category], 
        "TotalSales", 
        [TotalSalesPerProduct]
    ),
    [TotalSales]
)

Step 3: Identify Top Performing Product per Category

TopProductPerCategory = 
CALCULATE(
    MAX(Sales[ProductID]),
    FILTER(
        Sales,
        [TotalSalesPerProduct] = [MaxSalesPerCategory]
    )
)

Step 4: Combine Measures into a Visual-Friendly Table

Create a new table with the following calculated columns:

TopProductsTable = 
ADDCOLUMNS(
    SUMMARIZE(
        Sales, 
        Sales[Category]
    ),
    "ProductID", [TopProductPerCategory],
    "TotalSales", [MaxSalesPerCategory]
)

This practical approach ensures that the dataset can dynamically showcase the top-performing product in each category based on sales.

Data Mentor Advertisement

Related Posts

Comprehensive Data Analysis using Power BI and DAX

Data Model Discovery Library

An interactive web-based application to explore and understand various data model examples across multiple industries and business functions.