DAX - Data Analysis Expressions in Power BI  on Sample Dataset

What is DAX:

DAX (Data Analysis Expressions) is a formula language used in Power BI, Excel, and other Microsoft tools to perform data calculations and analysis. It allows users to define custom calculations and create complex expressions for aggregating, filtering, and analyzing data. DAX functions work on data models to create dynamic reports, support data insights, and provide advanced metrics like running totals, year-over-year growth, and conditional calculations.

Step-by-step process of using DAX (Data Analysis Expressions) to perform calculations and create measures on a sample sales dataset. We will explore several key DAX functions and demonstrate how to apply them to analyze and visualize the data in Power BI.

Sample Data Step-by-Step Guide on Using DAX (Open Dataset)

Step 1: Load Your Data into Power BI

1. Open Power BI.
2. Click on Home > Get Data > Excel (or CSV, etc.).
3. Browse and select your file that contains the data above.
4. Load the data into Power BI.

Step 2: Creating a Basic DAX Formula

Once the data is loaded, you can create DAX formulas to perform calculations on your data.

Let’s say you want to calculate the Total Sales for all the products.

1. Go to the Model view.
2. On the right, in the Fields pane, right-click on the dataset name (e.g., SalesData), and select New Measure.
3. In the formula bar, type:

Total Sales = SUM(SalesData[Sales])

This DAX formula will sum the values in the Sales column and create a new measure called Total Sales. In above query SalesData is the name of Table

4. Press Enter. The new measure will appear in the Fields pane

You can Create New Measure to calculate Total Cost as follows 

Total Cost = sum(SalesData[Cost])

You can Create New Measure to calculate Average Sales as follows 

Average Sales = AVERAGE(SalesData[sales])

You can Create New Measure to count number of products in table 

Count No. of Product = COUNT(SalesData[Product])

Create a Rank Measure for Products as follows

Product Rank = RANKX(ALL(SalesData[Product]), [Profit], , DESC, DENSE)

Where,

• RANKX assigns a ranking based on Profit.
• ALL(SalesData[Product]) ensures ranking is across all products.
• DESC sorts in descending order (highest profit first).
• DENSE ensures ranking continuity (e.g., 1,2,2,4 instead of 1,2,3,4).

Create a Measure to Filter Top 3 Products

Now, filter only the Top 3 products:

Top 3 Products = IF([Product Rank] <= 3, [Profit], BLANK())


Using TOPN Function
If you need a table for the Top 3 Products, create a new table:
Top3ProductsTable = TOPN(3, SalesData, [Profit], DESC)


Total Quantity Sold
Counts the total quantity of products sold.
Total Quantity = SUM(SalesData[Quantity])


Average Selling Price (ASP)
Calculates the average price per unit.
Average Selling Price = DIVIDE([Total Sales], [Total Quantity], 0)

DIVIDE function is used to avoid errors when dividing by zero

 Profit Margin (%)
Calculates the profit percentage.
Profit Margin = DIVIDE([Total Profit], [Total Sales], 0) * 100
Converts the ratio into a percentage.


Sales Growth (Month-over-Month)
Measures the percentage growth in sales from the previous month.

Sales Growth % = 
VAR PrevMonthSales = CALCULATE([Total Sales], PREVIOUSMONTH(SalesData[Date]))
RETURN DIVIDE(([Total Sales] - PrevMonthSales), PrevMonthSales, 0) * 100
PREVIOUSMONTH fetches last month's sales.
DIVIDE ensures no division-by-zero error.

Rank Products by Sales
Ranks products based on total sales.

Product Sales Rank = RANKX(ALL(SalesData[Product]), [Total Sales], , DESC, DENSE)
DESC for ranking in descending order.

Step 3: Using a Conditional Statement (e.g., Profit)

You might want to calculate the Profit, which is the difference between Sales and Cost.

1. Again, right-click on the table name and select New Measure.
2. Type the following DAX expression:

Profit = SUM(SalesData[Sales]) - SUM(SalesData[Cost])

This measure will subtract the sum of Cost from the sum of Sales.

Step 4: Filter by Region (Using CALCULATE)

Let’s say you want to calculate the Total Sales in the North Region.

1. Right-click on the table and select New Measure.
2. Use the following DAX formula:

North Sales = CALCULATE(SUM(SalesData[Sales]), SalesData[Region] = "North")

Here, the CALCULATE function changes the context to include only rows where Region equals "North," and then sums the Sales column.

Step 5: Creating a Running Total

A running total is helpful to see how values accumulate over time.

1. Right-click on the table and select New Measure.
2. Type the following formula:

Running Total Sales = CALCULATE(SUM(SalesData[Sales]), FILTER(ALL(SalesData), 

SalesData[Date] <= MAX(SalesData[Date])))

This formula calculates the cumulative total of sales from the first date to the current date.

Step 6: Using DAX in Visualizations

1. Go to the Report view.
2. Drag a Table visualization to the report canvas.
3. Add the following fields to the table:
   • Product
   • Total Sales
   • Profit
4. You can also create bar charts, pie charts, or line graphs by selecting the corresponding visualization type and adding the measures you created.

Step 7: Creating Time-based Measures

You can also create time-based measures. For instance, calculating Sales in the Last 7 Days:

1. Right-click on the table and select New Measure.
2. Use this formula:

Sales Last 7 Days = CALCULATE(SUM(SalesData[Sales]), DATESINPERIOD(SalesData[Date],

 MAX(SalesData[Date]), -7, DAY))

This DAX formula calculates the total sales for the last 7 days from the latest date in the dataset.

Last Month’s Sales

Fetches sales from the previous month.

Last Month Sales = CALCULATE([Total Sales], PREVIOUSMONTH(SalesData[Date]))

Step 8: Formatting Your Report

1. Select the visualizations and format them using the Format pane on the right.
2. You can change colors, labels, and axes to make your report more insightful.

Summary of Key DAX Functions Used

• SUM: Adds up a column's values.
• CALCULATE: Changes the context of a calculation.
• FILTER: Filters data based on a condition.
• DATESINPERIOD: Returns a table with dates within a given period.
• ALL: Removes filters from a table or column.
• MAX: Returns the maximum value in a column or table.

This should give you a basic understanding of DAX and its functionalities. You can now start creating more complex measures and calculations as needed. Let me know if you need more examples or explanations!