CALCULATE with multiple filters in Power BI - Dashboard Guide

Dashboard Mode - CALCULATE with multiple filters

Business Question

What is the total sales amount for the East region in 2023 for the Product Category 'Electronics'?

Sample Data

OrderID	Region	Year	Product Category	Sales Amount
1001	East	2023	Electronics	150
1002	West	2023	Electronics	200
1003	East	2022	Furniture	300
1004	East	2023	Furniture	100
1005	East	2023	Electronics	250
1006	West	2022	Electronics	400
1007	East	2023	Electronics	100

Dashboard Components

• KPI Card: Total Sales for East Region, 2023, Electronics
  Formula:
  Total Sales Filtered = CALCULATE(SUM('Sales'[Sales Amount]), 'Sales'[Region] = "East", 'Sales'[Year] = 2023, 'Sales'[Product Category] = "Electronics")
  Result: 150 + 250 + 100 = 500
• Bar Chart: Sales by Product Category for East Region in 2023
  Data:
  Electronics: 150 + 250 + 100 = 500
  Furniture: 100
  Formula for Electronics bar:
  CALCULATE(SUM('Sales'[Sales Amount]), 'Sales'[Region] = "East", 'Sales'[Year] = 2023, 'Sales'[Product Category] = "Electronics")
  Formula for Furniture bar:
  CALCULATE(SUM('Sales'[Sales Amount]), 'Sales'[Region] = "East", 'Sales'[Year] = 2023, 'Sales'[Product Category] = "Furniture")
• Table: All Sales Records for East Region in 2023
  Filter:
  Region = "East" AND Year = 2023
  Rows:
  OrderID 1001, 1004, 1005, 1007

Dashboard Layout

+-----------------------------+-----------------------------+
|         KPI Card            |         Bar Chart            |
|  Total Sales: $500          |  Electronics: $500           |
|                             |  Furniture: $100             |
+-----------------------------+-----------------------------+
|                             Table: Sales Records for East 2023  |
|  OrderID | Region | Year | Product Category | Sales Amount     |
|  1001    | East   | 2023 | Electronics      | 150              |
|  1004    | East   | 2023 | Furniture       | 100              |
|  1005    | East   | 2023 | Electronics      | 250              |
|  1007    | East   | 2023 | Electronics      | 100              |
+-------------------------------------------------------------+

Interactivity

The dashboard includes a slicer for Region and Year. Selecting East and 2023 filters all components:

• The KPI card recalculates total sales only for the selected region and year with the Electronics category filter applied in the formula.
• The bar chart updates to show sales by product category for the selected region and year.
• The table shows only sales records matching the selected region and year.

Self Check

Question: If you add a filter for Region = West, which components update and what changes occur?

Answer: All components update:

• The KPI card shows total sales for West region in 2023 for Electronics: only OrderID 1002 with $200.
• The bar chart shows sales by product category for West region in 2023: Electronics $200, Furniture $0.
• The table shows sales records for West region in 2023: only OrderID 1002.

Key Result

Dashboard shows total sales filtered by East region, year 2023, and Electronics category using CALCULATE with multiple filters.