MIN and MAX in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to know the minimum and maximum sales amounts for each product category in the last quarter.

📊 Data: You have a sales dataset with columns: Date, Product Category, Sales Amount.

🎯 Deliverable: Create a report showing each product category with its minimum and maximum sales amounts for the last quarter.

Sample Data

Date	Product Category	Sales Amount
2024-01-15	Electronics	1200
2024-02-10	Electronics	1500
2024-03-05	Electronics	1100
2024-01-20	Clothing	500
2024-02-15	Clothing	700
2024-03-10	Clothing	650
2024-01-25	Home Goods	800
2024-02-20	Home Goods	900
2024-03-15	Home Goods	850

1

Step 1: Filter the data to include only sales from the last quarter (January to March 2024).

Use Power BI filter on Date column: Date >= DATE(2024,1,1) and Date <= DATE(2024,3,31)

Expected Result

Filtered data contains only sales from January, February, and March 2024.

2

Step 2: Create a measure to calculate the minimum sales amount per product category.

Min Sales = MIN('Sales'[Sales Amount])

Expected Result

Measure returns the smallest sales amount for the selected category.

3

Step 3: Create a measure to calculate the maximum sales amount per product category.

Max Sales = MAX('Sales'[Sales Amount])

Expected Result

Measure returns the largest sales amount for the selected category.

4

Step 4: Create a table visual in Power BI with Product Category as rows, and add Min Sales and Max Sales measures as values.

Table visual configuration: Rows = 'Sales'[Product Category], Values = Min Sales, Max Sales

Expected Result

Table shows each product category with its minimum and maximum sales amounts for the last quarter.

Final Result

Product Category | Min Sales | Max Sales
-----------------|-----------|----------
Electronics      | 1100      | 1500     
Clothing         | 500       | 700      
Home Goods       | 800       | 900      

✓Electronics category had sales ranging from 1100 to 1500.

✓Clothing category had the lowest minimum sales of 500.

✓Home Goods category sales ranged between 800 and 900.

Bonus Challenge

Create a visual that shows the minimum and maximum sales amounts over time for each product category.

Show Hint

Use a line chart with Date on the x-axis, and create two measures for Min and Max sales filtered by date and category.

Practice

1.

What does the MIN function do in Power BI?

easy

A. It returns the smallest value in a column.

B. It returns the largest value in a column.

C. It counts the number of rows in a table.

D. It sums all values in a column.

Solution

1. Step 1: Understand the purpose of MIN

   The MIN function is designed to find the smallest value in a column of data.

2. Step 2: Compare with other functions

   Unlike MAX which finds the largest, MIN specifically returns the smallest value.

3. Final Answer:

   It returns the smallest value in a column. -> Option A

4. Quick Check:

   MIN = smallest value [OK]

Hint: MIN always finds the smallest number in a list [OK]

Common Mistakes:

• Confusing MIN with MAX
• Thinking MIN counts rows
• Assuming MIN sums values

2.

Which of the following is the correct syntax to find the maximum value in a column named Sales[Amount] using DAX?

easy

A. MAX(Sales[Amount])

B. MAXIMUM(Sales[Amount])

C. MAX_VALUE(Sales[Amount])

D. MAX(Sales.Amount)

Solution

1. Step 1: Recall correct DAX function name

   The correct function to find the largest value is MAX, not MAXIMUM or MAX_VALUE.

2. Step 2: Check column reference syntax

   In DAX, columns are referenced as TableName[ColumnName], so Sales[Amount] is correct.

3. Final Answer:

   MAX(Sales[Amount]) -> Option A

4. Quick Check:

   MAX + [Column] syntax [OK]

Hint: Use MAX(Table[Column]) to get largest value [OK]

Common Mistakes:

• Using wrong function names like MAXIMUM
• Using dot notation instead of brackets
• Adding extra words in function name

3.

Given the table Sales with Amount values: 100, 250, 75, 300, what is the result of MIN(Sales[Amount]) and MAX(Sales[Amount])?

medium

A. MIN = 75, MAX = 250

B. MIN = 100, MAX = 250

C. MIN = 0, MAX = 300

D. MIN = 75, MAX = 300

Solution

1. Step 1: Identify smallest value in Amount column

   The values are 100, 250, 75, 300. The smallest is 75.

2. Step 2: Identify largest value in Amount column

   The largest value among these is 300.

3. Final Answer:

   MIN = 75, MAX = 300 -> Option D

4. Quick Check:

   MIN = 75, MAX = 300 [OK]

Hint: MIN picks smallest, MAX picks largest number [OK]

Common Mistakes:

• Mixing up smallest and largest values
• Assuming zero is minimum without data
• Ignoring actual data values

4.

What is wrong with this DAX expression to find the minimum sales amount?
MIN(Sales.Amount)

medium

A. MIN cannot be used on numeric columns.

B. The table and column should be referenced with brackets, not dot notation.

C. The function should be MAX, not MIN.

D. The expression is correct as is.

Solution

1. Step 1: Check column reference syntax

   DAX requires columns to be referenced as TableName[ColumnName], not TableName.ColumnName.

2. Step 2: Validate function usage

   MIN is valid for numeric columns, so the function is correct but the syntax is wrong.

3. Final Answer:

   The table and column should be referenced with brackets, not dot notation. -> Option B

4. Quick Check:

   Use brackets for columns in DAX [OK]

Hint: Use brackets [ ] for columns, not dot . [OK]

Common Mistakes:

• Using dot notation instead of brackets
• Confusing MIN with MAX
• Thinking MIN can't be used on numbers

5.

You have a sales table with columns Region and SalesAmount. You want to create a measure that shows the maximum sales amount only for the East region. Which DAX expression is correct?

A) MAX(Sales[SalesAmount])
B) CALCULATE(MAX(Sales[SalesAmount]), FILTER(Sales[Region] = "East"))
C) MAXX(FILTER(Sales, Sales[Region] = "East"), Sales[SalesAmount])
D) MIN(Sales[SalesAmount])

hard

A. MAX(Sales[SalesAmount])

B. CALCULATE(MAX(Sales[SalesAmount]), FILTER(Sales[Region] = "East"))

C. MAXX(FILTER(Sales, Sales[Region] = "East"), Sales[SalesAmount])

D. MIN(Sales[SalesAmount])

Solution

1. Step 1: Understand filtering by region

   We want the max sales amount only for the East region, so we must filter the table to East.

2. Step 2: Evaluate options

   MAX(Sales[SalesAmount]) returns max for all regions.
   CALCULATE(MAX(Sales[SalesAmount]), FILTER(Sales[Region] = "East")) uses FILTER incorrectly; FILTER requires a table as the first argument followed by a condition.
   MAXX(FILTER(Sales, Sales[Region] = "East"), Sales[SalesAmount]) correctly filters Sales to East and then finds max SalesAmount.
   MIN returns minimum, not maximum.

3. Final Answer:

   MAXX(FILTER(Sales, Sales[Region] = "East"), Sales[SalesAmount]) -> Option C

4. Quick Check:

   Use MAXX + FILTER for conditional max [OK]

Hint: Use MAXX with FILTER for max with conditions [OK]

Common Mistakes:

• Using MAX without filtering
• Incorrect CALCULATE filter syntax
• Using MIN instead of MAX