Power BI Exercise - Using CALCULATE for Filtered Sales Measures (Retail Dataset)

Modify filter context to calculate focused sales results.

In this guided exercise, you will use the existing Sales table and basic measures to calculate sales for selected channels, regions, and categories. You will then validate the results in DAX Query View and test the measures with report slicers.

Learning goal: understand how CALCULATE evaluates a measure after changing its filter context.

CALCULATEFilter contextDAX Query ViewSlicers

Before you begin

Open the Power BI Desktop file containing the retail dataset. Confirm that the table is named Sales and that these measures already exist:

Total Sales = SUM ( Sales[SalesAmount] )

Total Quantity = SUM ( Sales[Quantity] )

Transaction Count = COUNTROWS ( Sales )

Control totals: Total Sales = 21,788, Total Quantity = 42, and Transaction Count = 15.

Introduction to CALCULATE

A normal measure uses the filters currently supplied by the report. For example, [Total Sales] returns sales for all rows when nothing is filtered, but it returns only Central sales after Central is selected in a Region slicer.

CALCULATE starts with an expression—usually an existing measure—and evaluates it in a modified filter context.

Filtered Measure =
CALCULATE (
    [Measure to evaluate],
    Filter condition
)

Part	Purpose
`[Measure to evaluate]`	The calculation whose result you want.
`Filter condition`	The row group that should be included, such as Online or Central.
Modified filter context	The final set of filters used while evaluating the measure.

Important: a simple CALCULATE filter adds a filter when that column is not already filtered. If the same column already has a filter, the new filter normally replaces it. You will observe this later with the Channel slicer.

Step 1: Create Online Sales

In the Data or Report view, select the Sales table.
Select New measure.
Enter the following DAX formula and press Enter.

Online Sales =
CALCULATE (
    [Total Sales],
    Sales[Channel] = "Online"
)

The measure evaluates [Total Sales] after applying Channel = Online. With no report filters, the result is 6,545.

Step 2: Create Store Sales

Store Sales =
CALCULATE (
    [Total Sales],
    Sales[Channel] = "Store"
)

This measure applies Channel = Store. The expected result is 15,243.

Step 3: Filter sales by region

Central Region Sales =
CALCULATE (
    [Total Sales],
    Sales[Region] = "Central"
)

The expected result is 9,281.

Step 4: Filter sales by category

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

The expected result is 5,959.

Step 5: Calculate the Online Sales percentage

Online Sales % =
DIVIDE (
    [Online Sales],
    [Total Sales]
)

Select the Online Sales % measure.
On the Measure tools ribbon, set Format to Percentage.
Set the number of decimal places to 2.

With no report filters, the result is 30.04%.

Step 6: Validate all measures in DAX Query View

Select the DAX Query View icon on the left side of Power BI Desktop.
Create a new query tab and enter the query below.
Select Run. No visual needs to be placed on the report canvas.

EVALUATE
ROW (
    "Total Sales", [Total Sales],
    "Online Sales", [Online Sales],
    "Store Sales", [Store Sales],
    "Central Region Sales", [Central Region Sales],
    "Computing Sales", [Computing Sales],
    "Online Sales %", [Online Sales %]
)

Measure	Expected result
Total Sales	21,788
Online Sales	6,545
Store Sales	15,243
Central Region Sales	9,281
Computing Sales	5,959
Online Sales %	0.3003965... in the result grid; 30.04% when formatted

DAX Query View returns raw query results and may not display the measure's currency or percentage formatting. The underlying value is still correct.

Step 7: Add the measures to the dashboard

Return to Report view.
Add four Card visuals.
Place Online Sales, Store Sales, Central Region Sales, and Online Sales % in separate cards.
Give each card a clear title.
Format sales values as your preferred currency or whole number and keep the percentage at two decimal places.

Step 8: Test filter behaviour with slicers

Test A: Region slicer

Add a slicer using Sales[Region]. Select Central. Because Region is a different column from Channel, it works together with each Channel filter.

Measure	Expected with Central selected
Total Sales	9,281
Online Sales	3,116
Store Sales	6,165
Online Sales %	33.57%

Test B: Channel slicer

Add a slicer using Sales[Channel]. This test demonstrates filter replacement because the slicer and the filtered measures all target the same column.

Channel selection	Total Sales	Online Sales	Store Sales	Online Sales %
No selection	21,788	6,545	15,243	30.04%
Online	6,545	6,545	15,243	100.00%
Store	15,243	6,545	15,243	42.94%

Why can Online Sales still appear when Store is selected? The Online Sales measure replaces the existing filter on Sales[Channel] with Online. Meanwhile, Total Sales keeps the slicer's Store filter. This also explains the percentage result. The behavior is correct, but the percentage may not communicate what a report reader expects.

Optional improvement: a stable share of all channels

If the intended denominator is always the total across every channel—while keeping Region and Category filters—remove only the Channel filter in the denominator:

Online Share of All Channels =
DIVIDE (
    [Online Sales],
    CALCULATE (
        [Total Sales],
        REMOVEFILTERS ( Sales[Channel] )
    )
)

With no Region or Category filter, this measure remains 30.04% even when a Channel slicer selection is changed.

Knowledge check

What expression is evaluated inside the Online Sales measure?
What filter does the measure add or replace?
Why does a Region slicer combine with the Online filter?
Why can the Channel slicer be replaced by the measure?
What does REMOVEFILTERS ( Sales[Channel] ) do in the optional measure?

Troubleshooting

Problem	Check
The formula cannot find `Sales`.	Confirm the imported table is named Sales.
A measure returns blank.	Clear report filters and verify the Channel, Region, and Category text values.
Online Sales % appears as a decimal.	Set the measure format to Percentage in Measure tools.
The query reports an unknown measure.	Create and save every model measure before running the query.
Values differ from the control totals.	Confirm that all 15 retail records were loaded and that SalesAmount is numeric.

Exercise complete

You have created reusable filtered measures, validated them without adding visuals, and observed how CALCULATE changes filter context. These ideas are the foundation for more advanced DAX patterns.

Completion checklist

Five new measures were created and formatted.
Every unfiltered value matches the expected result.
The measures were validated in DAX Query View.
Cards and slicers were added to the report.
Channel and Region filter behaviour was tested.

InfoTech