Power BI Exercise - Creating Basic DAX Measures (Retail Dataset)

Create reusable calculations for sales, quantity, transaction count, and average sale value.

Exercise objectives

Understand the purpose of a DAX measure.
Create four basic measures using SUM, DISTINCTCOUNT, and DIVIDE.
Format currency and numeric measures correctly.
Display the measures using card visuals.
Test how the measures respond to Region and Channel filters.

Introduction to DAX

DAX stands for Data Analysis Expressions. It is the formula language used by Power BI to create calculations from data that has already been loaded into the model.

DAX may look similar to an Excel formula, but it works with tables, columns, relationships, and report filters. In this exercise, DAX will be used to create measures.

What is a measure?

A measure is a named calculation that Power BI evaluates when it is needed. Its result can change when a user selects a slicer, applies a filter, or views a different category.

For example, the same Total Sales measure can return:

RM21,788 when no filter is applied.
RM9,281 when Region is filtered to Central.
RM6,545 when Channel is filtered to Online.

Important: A measure does not store one permanent result inside the table. Power BI recalculates it according to the current filter context.

Basic structure of a DAX measure

Total Sales =
SUM ( Sales[SalesAmount] )

Formula part	Example	Meaning
Measure name	Total Sales	The name displayed in the Data pane
Equals sign	=	Separates the measure name from its calculation
DAX function	SUM	Adds the values in a numeric column
Table name	Sales	The table containing the required column
Column name	[SalesAmount]	The column whose values will be added

DAX functions used in this exercise

Function	Purpose	Example use
SUM	Adds all visible values in a numeric column	Total sales and total quantity
DISTINCTCOUNT	Counts unique values	Number of SaleID transactions
DIVIDE	Divides two values and safely handles a zero denominator	Average sale value

Measure compared with a calculated column

Measure	Calculated column
Produces a result when used in a query, visual, or filter	Produces and stores a value for every table row
Responds to report filter context	Calculated row by row during data refresh
Best for totals, averages, ratios, and KPIs	Best for row-level labels, categories, and values needed for relationships
Shown with a calculator icon	Shown as a regular column field

Key idea: Columns provide the data, while measures answer questions about that data.

Before you begin

This exercise continues from the retail dataset exercise. Your Power BI file should contain a table named Sales with 15 rows and the following columns:

Required columns	Control value
SaleID, SaleDate, Product, Category, Region, Channel, Quantity, UnitPrice, SalesAmount	15 rows
SalesAmount	RM21,788 total

Step 1: Create the Total Sales measure

In the Data pane, select the Sales table.
On the ribbon, select Modeling → New measure.
Replace the default formula with the DAX below.
Press Enter.

Total Sales =
SUM ( Sales[SalesAmount] )

Format the measure

Select Total Sales in the Data pane.
Open Measure tools.
Set the format to Currency.
Select Malaysian Ringgit or enter RM as the currency symbol.
Set decimal places to 0.

View the result without adding a visual

Select the DAX Query View icon on the left side of Power BI Desktop.
In the Data pane, expand the Sales table.
Right-click the Total Sales measure.
Select Quick queries → Evaluate.
Power BI creates and runs a query similar to the following:

EVALUATE
    SUMMARIZECOLUMNS(
        "Total Sales", [Total Sales]
    )

If the query does not run automatically, select Run.
Look at the Results grid below the query editor.
Confirm that Total Sales returns 21788.

Formatting note: DAX Query View can display the raw value 21788 instead of the formatted value RM21,788. Both represent the same measure result.

Expected result: Total Sales = RM21,788.

Step 2: Create the Total Quantity measure

Create another measure in the Sales table:

Total Quantity =
SUM ( Sales[Quantity] )

Format this measure as a whole number with no decimal places.

Expected result: Total Quantity = 42.

Step 3: Create the Transaction Count measure

Each transaction has a unique SaleID. Use DISTINCTCOUNT so that each SaleID is counted only once.

Transaction Count =
DISTINCTCOUNT ( Sales[SaleID] )

Format this measure as a whole number.

Expected result: Transaction Count = 15.

Step 4: Create the Average Sale Value measure

Calculate the average value of one transaction by dividing Total Sales by Transaction Count.

Average Sale Value =
DIVIDE ( [Total Sales], [Transaction Count] )

Format this measure as currency with two decimal places.

Why use DIVIDE? DIVIDE safely handles a zero or blank denominator. This is generally preferable to using the division operator for report measures.

Expected result: Average Sale Value = RM1,452.53.

Verify the four measures

RM21,788Total Sales

42Total Quantity

15Transaction Count

RM1,452.53Average Sale Value

Step 5: Create card visuals

Open Report view.
Select the Card visual.
Drag Total Sales into the card.
Create three more cards for Total Quantity, Transaction Count, and Average Sale Value.
Arrange the four cards in one row.
Give each card a clear title and check its number format.

Step 6: Test filter context

Add a Slicer visual.
Add Region to the slicer.
Add a second slicer using Channel.
Select different values and confirm that all four cards change.
Clear both slicers to restore the full-dataset results.

Filter checkpoints

Filter	Total Sales	Total Quantity	Transaction Count	Average Sale Value
No filter	RM21,788	42	15	RM1,452.53
Region = Central	RM9,281	9	5	RM1,856.20
Channel = Online	RM6,545	25	7	RM935.00
Channel = Store	RM15,243	17	8	RM1,905.38

Troubleshooting

Problem	Likely cause	Correction
The formula cannot find Sales	The imported table has another name	Rename the table to Sales or use its current name in the formula
Total Sales is incorrect	SalesAmount has the wrong data type	Set SalesAmount to Fixed decimal number
Transaction Count is 42	Quantity was counted instead of SaleID	Use DISTINCTCOUNT on Sales[SaleID]
Average Sale Value produces an error	The measure names do not match	Use the exact names Total Sales and Transaction Count
Cards do not respond to a slicer	Visual interaction may be disabled	Use Edit interactions and enable filtering for each card

Completion checklist

Four measures have been created in the Sales table.
Currency and whole-number formats are correct.
Four card visuals show the expected unfiltered results.
Region and Channel slicers update all four cards.
The filter checkpoint results match the values shown above.

Exercise complete: You have created reusable DAX measures and confirmed that they respond correctly to filter context.