Ravindra BagaleCourses & study guides मराठी Track your progress

13. DAX: Data Analysis Expressions

13.8 CALCULATE in Power BI with Example

By Last updated:

Quick answer: the short, self-contained version of this lesson is the guide Intermediate DAX Patterns in Power BI.

CALCULATE in Power BI with example — in 30 seconds

What: CALCULATE runs a measure again with filters you choose.

Why: The manager looks at Nashik but wants a Pune benchmark card on the same page.

Tiny example: City slicer = Nashik. Total Sales = ₹300. Pune Sales (CALCULATE) still = ₹1,200.

First look at the data (before, then after):

CALCULATE in Power BI with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Orders (3 rows) · City slicer will be Nashik

Order ID City Platform Amount (₹)
101 Pune Blinkit 500
102 Nashik Blinkit 300
103 Pune Amazon Now 700

About this data: This table uses columns: Order ID, City, Platform, Amount (₹). This table uses columns: Measure, No slicer, Slicer = Nashik. This table uses columns: Measure, Value (₹ or count). Values shown are the lesson examples already on this page (not a new dataset).

Formula / click path

Pune Sales = CALCULATE([Total Sales], Orders[City] = "Pune")
Blinkit Sales = CALCULATE([Total Sales], Orders[Platform] = "Blinkit")

After — Cards (slicer on Orders[City])

Measure No slicer Slicer = Nashik
Total Sales ₹1,500 ₹300
Pune Sales ₹1,200 ₹1,200
Blinkit Sales ₹800 ₹300

Do it step by step:

  1. Make sure [Total Sales] exists.
  2. Click Home › New measure. Type the Pune Sales formula.
  3. Click New measure again. Type Blinkit Sales.
  4. Add a City slicer. Select Nashik.
  5. Add three cards. Check: ₹300, ₹1,200, ₹300.
  6. Say why: the City filter inside CALCULATE replaces the slicer on City. The Platform filter does not touch City.

What appears on screen after these steps — sample layout, fictional data

What you should see on screen after the steps (sample layout — fictional data, not a Microsoft screenshot).

Start with a problem

Amir looks at a fictional Blinkit vs Amazon Now class file for Pune and Nashik. The City slicer is on Nashik. He needs a card that always shows Pune sales for comparison ("how is Pune doing while I look at Nashik?"). He writes:

Pune Sales = CALCULATE([Total Sales], DarkStore[City] = "Pune")

It works — and then he is surprised that a second measure meant to mean "Blinkit sales within the slicer city" also ignores the slicer. He has met replace vs intersect filter behaviour.

Explain simply

CALCULATE(<expression>, <filter1>, <filter2>, …) runs an expression in a changed filter context. Its filters can add, replace or remove filters.

  • A simple filter like Orders[Platform] = "Blinkit" replaces any filter already on that same column.
  • Many filter arguments combine with AND.
  • Wrap it in KEEPFILTERS(...) when you want to intersect with the slicer instead of replacing it.
  • Filter modifiers such as ALL, REMOVEFILTERS, USERELATIONSHIP and CROSSFILTER also go inside CALCULATE.

Show the data

Fictional three orders again, plus Platform for CALCULATE demos:

Order ID City Platform Amount (₹)
101 Pune Blinkit 500
102 Nashik Blinkit 300
103 Pune Amazon Now 700

Hand checks:

  • Total Sales All = 1,500
  • Blinkit Sales = 500+300 = 800
  • Pune Sales = 500+700 = 1,200
  • Blinkit and Dairy would need a Product table — keep Platform-only here.

Demonstrate

  1. Ensure [Total Sales] exists.
  2. New measure → enter Blinkit Sales (below), then Enter. Why: replace Platform filter with Blinkit.
  3. New measure, then Pune & Nashik Orders with IN { "Pune", "Nashik" } if City is on DarkStore — or Orders[City] if City is on the fact in the tiny demo.
  4. Put City on a slicer, select Nashik, and place Blinkit Sales and Total Sales on cards. Blinkit Sales still sums Blinkit rows that survive other filters; Platform filter is forced to Blinkit.
  5. Create Blinkit Sales Keep = CALCULATE([Total Sales], KEEPFILTERS(Orders[Platform] = "Blinkit")) and compare when a Platform slicer is on Amazon Now (Keep can return blank; replace still shows Blinkit).
Blinkit Sales = CALCULATE([Total Sales], Orders[Platform] = "Blinkit")

Amazon Now Sales = CALCULATE([Total Sales], Orders[Platform] = "Amazon Now")

Pune & Nashik Orders = CALCULATE([Total Orders], DarkStore[City] IN { "Pune", "Nashik" })

Blinkit Dairy Sales =
CALCULATE(
    [Total Sales],
    Orders[Platform] = "Blinkit",
    Product[Category] = "Dairy & Breakfast"
)

UPI Orders = CALCULATE([Total Orders], Orders[Payment Mode] = "UPI")

Discounted Sales = CALCULATE([Total Sales], Orders[Discount] > 0)

Show the result

Your result should look like this (fictional sample, no slicer):

Measure Value (₹ or count)
Total Sales 1,500
Blinkit Sales 800
Amazon Now Sales 700
Pune via CALCULATE City = "Pune" 1,200

With City slicer = Nashik: Total Sales, then 300; CALCULATE([Total Sales], City = "Pune") → 1,200 (City filter replaced).

Explain what happened

CALCULATE first builds a new filter context from the old one. It applies each filter argument (replace or change). Then it runs the expression. That is why Pune sales can appear on a Nashik page: the City filter was replaced. Context transition (13.3) is the same engine when CALCULATE sees a row context.

Explore further

  • ALL(table) / REMOVEFILTERS inside CALCULATE for %-of-total (13.10).
  • USERELATIONSHIP for inactive date relationships (13.12).
  • Prefer column filters over FILTER(whole fact table, ...) for speed (13.9).

Fix common mistakes

Wrong result Reason Correction
Expected slicer city AND Pune Simple filter replaces City Use KEEPFILTERS to intersect, or remove the CALCULATE City filter
Slow refresh / visual FILTER(Orders, ...) on full fact Use Orders[Discount] > 0 style column filters
Blank when student expected a number Filters disagree (KEEPFILTERS + opposite slicer) Teach replace vs keep with the Platform demo

Practise independently

Practice task

On the three-order table, write Blinkit Sales and Pune Sales with CALCULATE. Predict values with slicer City = Nashik before you click.

Hints
  • Blinkit = 800; Pune CALCULATE = 1200 even when slicer is Nashik.
  • Total Sales with Nashik slicer = 300.
Solution 
Blinkit Sales = CALCULATE([Total Sales], Orders[Platform] = "Blinkit")
Pune Sales = CALCULATE([Total Sales], Orders[City] = "Pune")

Nashik slicer: Total Sales 300, Pune Sales 1200, Blinkit Sales 300 (only order 102 is Blinkit in Nashik).

Think like a professional

Business: a "Pune benchmark" card beside a Nashik view is a deliberate replace filter — document it so nobody thinks the slicer is broken. Performance: CALCULATE itself is cheap; expensive table filters inside it are not. Interview: explain replace vs KEEPFILTERS with a two-city, two-platform table on the whiteboard.

Ravindra Bagale's Tip

The mistake I see most often with CALCULATE is expecting a filter argument to add to the slicer selection, when it actually replaces the filter on that column. CALCULATE([Total Sales], DarkStore[City] = "Pune") shows Pune even when Nashik is selected. Use KEEPFILTERS when you want to intersect instead. Keep this in mind!

Loading DAX practice tasks...

  1. Pune sales (Easy): Write Pune Sales = [Total Sales] for Store City "Pune".
  2. Dairy units (Easy): Write Dairy Qty = [Total Qty] for Product Category "Dairy".
  3. Blinkit orders (Easy): Write Blinkit Orders = [Total Orders] for Platform "Blinkit".
  4. Two filters = AND (Medium): Write Mumbai Delivered = [Total Sales] for City "Mumbai" and Status "Delivered".
  5. Two values of one column (IN) (Medium): Write Pune or Mumbai = [Total Sales] where City is Pune or Mumbai.
  6. CALCULATE overrides a slicer (Medium): Slicer: City = Nagpur. Write Pune Sales = [Total Sales] for City "Pune". The answer is still Pune's sales.
  7. This financial year (Medium): Write FY26 Sales = [Total Sales] for 'Date'[FY] = "FY 2025-26".
  8. Premium share by city (Hard): Row per Store City. Write Premium Share = sales from Premium customers ÷ all sales.
  9. Filter with a number test (Hard): Write Bulk Sales = [Total Sales] for lines with Qty of 3 or more.
  10. Ignore the platform slicer (Hard): Slicer: Platform = Zepto. Write Pune All Platforms = Pune sales for every platform (ignore the platform slicer).

See all Power BI practice tasks

Ravi Sir's Cards for this lesson

CALCULATE: change the filter inside a measure. A measure is a formula that gives one number, like total revenue. Sometimes you need that number for one fixed city, whatever the user clicks.Revision card

मराठी cards