SUMX and iterators in Power BI - Dashboard Guide

Dashboard Mode - SUMX and iterators

Business Question

How much total revenue did each product category generate, considering the quantity sold and price per unit?

Sample Data

Product Category	Product	Quantity Sold	Price Per Unit
Electronics	Headphones	10	50
Electronics	Keyboard	5	80
Furniture	Chair	7	120
Furniture	Desk	3	200
Clothing	T-Shirt	20	15
Clothing	Jeans	10	40

Dashboard Components

• KPI Card: Total Revenue
  Formula: Total Revenue = SUMX(Sales, Sales[Quantity Sold] * Sales[Price Per Unit])
  Result: 10*50 + 5*80 + 7*120 + 3*200 + 20*15 + 10*40 = 500 + 400 + 840 + 600 + 300 + 400 = 3040
• Bar Chart: Revenue by Product Category
  Formula: Revenue by Category = SUMX(FILTER(Sales, Sales[Product Category] = EARLIER(Sales[Product Category])), Sales[Quantity Sold] * Sales[Price Per Unit])
  Values:
  • Electronics: (10*50)+(5*80) = 500 + 400 = 900
  • Furniture: (7*120)+(3*200) = 840 + 600 = 1440
  • Clothing: (20*15)+(10*40) = 300 + 400 = 700
• Table: Detailed Sales with Revenue
  Added Column Formula: Revenue = Sales[Quantity Sold] * Sales[Price Per Unit]
  Example Row: Headphones: 10 * 50 = 500

Dashboard Layout

+----------------------+-----------------------+
|      Total Revenue    |  Revenue by Category  |
|        (KPI Card)    |      (Bar Chart)      |
+----------------------+-----------------------+
|          Detailed Sales Table (with Revenue)          |
+-------------------------------------------------------+

Interactivity

A slicer for Product Category filters all components:

• KPI Card updates total revenue for the selected category.
• Bar Chart highlights or filters to the selected category.
• Detailed Sales Table shows only products from the selected category.

This helps focus analysis on one category at a time.

Self Check

If you add a filter for Product Category = Furniture, which components update and what are their new values?

• KPI Card: Total Revenue updates to 1440.
• Bar Chart: Shows only Furniture category bar with value 1440.
• Detailed Sales Table: Shows only Chair and Desk rows with their revenues.

Key Result

Dashboard shows total and category-wise revenue using SUMX to multiply quantity and price.