Q1: Aggregation Formulas — SUM, AVERAGE, MIN, MAX and COUNTROWS

Question: You have a Sales table containing SalesAmount, Quantity and OrderID. Create measures for total sales, average sales, minimum sale, maximum sale and total orders.

Step 1: Create a Total Sales measure using SUM().

Total Sales = SUM(Sales[SalesAmount])

Step 2: Calculate the average transaction value using AVERAGE().

Average Sales = AVERAGE(Sales[SalesAmount])

Step 3: Find the smallest and largest transaction.

Minimum Sale = MIN(Sales[SalesAmount])
Maximum Sale = MAX(Sales[SalesAmount])

Step 4: Count the number of sales records.

Total Orders = COUNTROWS(Sales)

Logic: Aggregation functions summarize numerical or tabular data and are commonly used in Power BI KPI cards, tables and charts.

Q2: Mathematical Formulas — DIVIDE, ROUND and ABS

Question: Calculate profit margin, round the result to two decimal places and find the absolute difference between sales and cost.

Step 1: Create Sales and Cost measures.

Total Sales = SUM(Sales[SalesAmount])
Total Cost = SUM(Sales[CostAmount])

Step 2: Calculate profit.

Total Profit = [Total Sales] - [Total Cost]

Step 3: Calculate profit margin safely using DIVIDE().

Profit Margin = DIVIDE([Total Profit], [Total Sales], 0)

Step 4: Round the profit margin.

Profit Margin Rounded = ROUND([Profit Margin], 2)

Step 5: Calculate the absolute difference between sales and cost.

Sales Cost Difference = ABS([Total Sales] - [Total Cost])

Logic: Mathematical functions are useful for financial calculations, percentages, variance analysis and dashboard KPIs.

Q3: Logical Formulas — IF, AND and OR

Question: Classify each customer as Premium when SalesAmount is at least 50000, otherwise classify them as Regular.

Step 1: Create a calculated column using IF().

Customer Type = IF(
    Sales[SalesAmount] >= 50000,
    "Premium",
    "Regular"
)

Step 2: Create a more detailed classification using AND().

Sales Status = IF(
    AND(Sales[SalesAmount] >= 50000, Sales[Quantity] >= 10),
    "High Value",
    "Normal"
)

Step 3: Use OR() when either condition should qualify.

Priority Sale = IF(
    OR(Sales[SalesAmount] >= 50000, Sales[Quantity] >= 20),
    "Priority",
    "Normal"
)

Logic: Logical functions help create business rules and classifications from multiple conditions.

Q4: Text Formulas — LEFT, RIGHT, MID and CONCATENATE

Question: A CustomerCode contains values such as "DEH2026001". Extract the first two characters, last three characters and combine customer information into a single label.

Step 1: Extract the first two characters using LEFT().

City Code = LEFT(Customer[CustomerCode], 2)

Step 2: Extract the last three characters using RIGHT().

Customer Number = RIGHT(Customer[CustomerCode], 3)

Step 3: Extract characters from the middle using MID().

Year Code = MID(Customer[CustomerCode], 3, 4)

Step 4: Combine first and last name.

Customer Label = CONCATENATE(Customer[FirstName], " ", Customer[LastName])

Logic: Text functions are useful for cleaning codes, extracting identifiers and creating readable labels.

Q5: Filter Functions — CALCULATE, FILTER and ALL

Question: Calculate total sales for Electronics and compare it with overall sales without the current product filter.

Step 1: Create the basic sales measure.

Total Sales = SUM(Sales[SalesAmount])

Step 2: Use CALCULATE() to filter Electronics sales.

Electronics Sales = CALCULATE(
    [Total Sales],
    Sales[Category] = "Electronics"
)

Step 3: Use FILTER() when a more complex condition is required.

High Value Sales = CALCULATE(
    [Total Sales],
    FILTER(Sales, Sales[SalesAmount] > 50000)
)

Step 4: Use ALL() to calculate sales without the current Product filter.

All Product Sales = CALCULATE(
    [Total Sales],
    ALL(Sales[Product])
)

Logic: Filter functions control the filter context used by DAX calculations and are essential for advanced Power BI analysis.

Q6: Date and Time Formulas — YEAR, MONTH, DAY and DATE

Question: Extract the year, month and day from an OrderDate and create a custom date.

Step 1: Extract the year.

Order Year = YEAR(Sales[OrderDate])

Step 2: Extract the month number.

Order Month = MONTH(Sales[OrderDate])

Step 3: Extract the day.

Order Day = DAY(Sales[OrderDate])

Step 4: Create a date value using DATE().

Financial Year Start = DATE(YEAR(Sales[OrderDate]), 4, 1)

Logic: Date functions help transform date columns into useful reporting attributes for monthly, yearly and financial-year analysis.

Q7: Time Intelligence — DATEADD and SAMEPERIODLASTYEAR

Question: Calculate previous-year sales and compare them with the current year's sales. A proper Date table should be related to the Sales table.

Step 1: Create Total Sales.

Total Sales = SUM(Sales[SalesAmount])

Step 2: Calculate previous-year sales using DATEADD().

Previous Year Sales = CALCULATE(
    [Total Sales],
    DATEADD('Date'[Date], -1, YEAR)
)

Step 3: Alternatively use SAMEPERIODLASTYEAR().

Previous Year Sales 2 = CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

Step 4: Calculate year-over-year growth.

YoY Growth % = DIVIDE(
    [Total Sales] - [Previous Year Sales],
    [Previous Year Sales],
    0
)

Logic: Time-intelligence functions allow users to compare current periods with previous years, months and other time periods.

Q8: Statistical Formulas — MEDIAN, DISTINCTCOUNT and COUNT

Question: Analyze customer and transaction data by finding the median order value, unique customers and number of numeric entries.

Step 1: Calculate median order value.

Median Order Value = MEDIAN(Sales[SalesAmount])

Step 2: Count unique customers.

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Step 3: Count numeric values in SalesAmount.

Sales Records = COUNT(Sales[SalesAmount])

Logic: Statistical and counting functions help analyze distributions, unique entities and data volume.

Q9: Ranking Formula — RANKX

Question: Rank products according to their total sales.

Step 1: Create Total Sales.

Total Sales = SUM(Sales[SalesAmount])

Step 2: Use RANKX() to rank products from highest to lowest sales.

Product Rank = RANKX(
    ALL(Sales[Product]),
    [Total Sales],
    ,
    DESC,
    DENSE
)

Step 3: Add Product and Product Rank to a table visual.

Logic: RANKX() is useful for identifying top-performing products, customers, salespeople or regions.

Q10: Iterator Formulas — SUMX

Question: The Sales table contains Quantity and UnitPrice. Calculate total revenue by multiplying quantity by unit price for every row.

Step 1: Create a measure using SUMX().

Total Revenue = SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

Step 2: Add Total Revenue to a Card or chart.

Logic: SUMX() evaluates an expression row by row and then adds the results. Iterator functions such as SUMX are useful when the calculation requires multiple columns from each row.

Q11: Relationship Formula — RELATED

Question: You have a Sales table related to a Product table. Bring the Product Category from the Product table into the Sales table.

Step 1: Ensure a valid relationship exists between Sales and Product.

Step 2: Create a calculated column in the Sales table.

Product Category = RELATED(Product[Category])

Step 3: Use Product Category in sales analysis.

Logic: RELATED() retrieves a value from a related table when the required relationship exists in the data model.

Q12: Running Total Using CALCULATE, FILTER and MAX

Question: Create a cumulative sales total that increases as the reporting date progresses.

Step 1: Create Total Sales.

Total Sales = SUM(Sales[SalesAmount])

Step 2: Create the running total.

Running Total Sales = CALCULATE(
    [Total Sales],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Step 3: Add Date to the X-axis of a line chart and Running Total Sales to the Values field.

Logic: ALL() removes the date filter, FILTER() keeps dates up to the current date, and MAX() identifies the current date in the visual context.