# Understanding the MAXX Function in DAX

## Setup Instructions

## Practical Implementation

### Example Dataset

Assume we have a dataset named `Sales`

:

`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:

```
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**:

`ProductID`

and `MaxSalesAmountPerProduct`

.**Card Visualization**:

`MaxSalesAmountOverall`

to a card.**Line Chart**:

`Year`

`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.

# 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.