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

13. DAX: Data Analysis Expressions

13.24 Weighted Averages and FILTER inside an Iterator

By Last updated:

Weighted average in DAX with example — in 30 seconds

What: A weighted average gives more weight to rows with more quantity.

Why: AVERAGE of price per kg treats a 1 kg order and a 9 kg order the same. The real average price is different.

Tiny example: 1 kg at ₹100 and 9 kg at ₹60. Simple average = ₹80. Weighted average = ₹64.

First look at the data (before → after):

Weighted average in DAX with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Onion orders, Nashik (fictional)

Order Qty (kg) Price per kg (₹)
A 1 100
B 9 60

Formula / click path

Simple Avg   = AVERAGE(Orders[Price per kg])
Weighted Avg = DIVIDE(SUMX(Orders, Orders[Qty] * Orders[Price per kg]), SUM(Orders[Qty]))

After — Result

Measure Calculation Result
Simple Avg (100 + 60) ÷ 2 ₹80
Weighted Avg (1 × 100 + 9 × 60) ÷ 10 = 640 ÷ 10 ₹64

Do it step by step:

  1. Enter the two rows with Home › Enter data.
  2. Create Simple Avg with AVERAGE. The card shows ₹80.
  3. Create Weighted Avg with SUMX and DIVIDE.
  4. Put both in cards. Check: ₹80 and ₹64.
  5. Ask which number the business really paid: ₹640 for 10 kg is ₹64 per kg.

SUM is not right everywhere. Weight means: whoever bought more quantity should count more in the price.

A simple AVERAGE(Product[Unit Price]) gives each product one vote. A weighted average gives each unit sold one vote.

Weighted Avg Unit Price =
DIVIDE(
    SUMX(Orders, Orders[Quantity] * RELATED(Product[Unit Price])),
    SUM(Orders[Quantity])
)

COUNTX counts rows where the expression is not blank: COUNTX(Orders, Orders[Discount]) counts lines that have a discount. MINX and MAXX pick the smallest or largest result of a row expression. You already saw MAXX in section 13.6.

FILTER inside an iterator is allowed, and it is the slow version of a column filter. Learn it so you can read other people's code, then prefer CALCULATE when the test is a column.

Snack Line Sales (iterator) =
SUMX(
    FILTER(Orders, RELATED(Product[Category]) = "Snacks"),
    Orders[Amount]
)

Snack Line Sales (better) =
CALCULATE([Total Sales], Product[Category] = "Snacks")

The second one uses the relationship and does not scan Orders with RELATED on every row. Same idea on this model when the category really is on Product.

Worked example, fictional lines:

Product Unit Price Quantity City
Shrikhand 80 1 Pune
Shrikhand 80 10 Nashik
Chakli 40 1 Kolhapur

AVERAGE of the two unit prices is (80+40)/2 = 60. The weighted average is (80×1 + 80×10 + 40×1) / 12 = 920/12 = 76.67. Nashik's ten tubs pull the average toward 80. If a manager says "average price is 60", they are averaging the catalogue, not the sales.

Ravindra Bagale's Tip

A common mistake is taking AVERAGE of a price column and calling it the average selling price. If the business means "per unit sold", weight it with SUMX of quantity times price, divided by quantity. And do not stop at writing FILTER(Orders, ...) when a column filter in CALCULATE is enough. Remember this rule.

Loading DAX practice tasks...

  1. Simple average cost (Easy): Measure: Simple Avg Cost = average of CostPerUnit over batches.
  2. Weighted average cost (Easy): Measure: Weighted Cost = SUMX of UnitsMade × CostPerUnit ÷ SUM of UnitsMade. Use DIVIDE.
  3. Weighted cost per part (Easy): Shown per Part. Measure: Weighted Cost as in the last task.
  4. Reject rate per plant (Medium): Shown per Plant. Measure: Reject Rate = SUM of UnitsRejected ÷ SUM of UnitsMade.
  5. Average of batch rates (Medium): Measure: Avg Batch Rate = average over batches of UnitsRejected ÷ UnitsMade.
  6. Weighted cost per country (Medium): Shown per Country. Measure: Weighted Cost.
  7. Night shift cost (Medium): Measure: Night Cost = SUMX over Night-shift batches of UnitsMade × CostPerUnit.
  8. Cost per good unit (Hard): Shown per Plant. Measure: Good Unit Cost = SUMX of UnitsMade × CostPerUnit ÷ SUMX of UnitsMade − UnitsRejected.
  9. Weighted cost of big batches (Hard): Measure: Big Batch Cost = weighted cost only for batches with UnitsMade of 400 or more.
  10. Weighted minus simple (Hard): Shown per Part. Measure: Avg Gap = weighted cost − AVERAGE of CostPerUnit.

See all Power BI practice tasks

Ravi Sir's Cards for this lesson

Weighted averages and FILTER inside an iterator. A simple average of prices (₹315) is misleading when quantities differ.Revision card

मराठी cards