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

24. Power Pivot and the Data Model

24.7 DAX Measures: SUM, COUNTROWS, DISTINCTCOUNT and CALCULATE

By Last updated:

Example first. In the model, a measure is a named formula that works in any PivotTable layout. Ravindra Bagale writes four: Total Sales = ₹2,315, Order Count = 12, Customers = 6, and Pune Sales = ₹940. Drop them into a PivotTable by City and they recalculate for every row.

DAX measures — in 30 seconds

What: A DAX measure is a formula stored in the Data Model, such as Total Sales := SUM(Orders[Amount]). It has no fixed cell; it calculates for whatever filters the PivotTable row, column or slicer gives it.

Why: Write the logic once and reuse it everywhere. Distinct counts and "this city only" figures are hard with normal formulas but take one line in DAX.

Tiny example: Customers := DISTINCTCOUNT(Orders[CustomerID]) gives 6. By City the rows add up to 7, because one customer bought in two cities.

First look at the data (before → after):

DAX measures: before and after tables, fictional Blinkit Maharashtra data

Expected result — PivotTable from the model with measures

City Total Sales (₹) Order Count Customers
Pune 940 5 3
Nashik 670 3 2
Nagpur 355 2 1
Solapur 350 2 1
Grand Total 2,315 12 6
Before table and formula as text

Before — Normal PivotTable values (no measures)

City Sum of Amount (₹) Count of CustomerID
Pune 940 5
Nashik 670 3
Nagpur 355 2
Solapur 350 2
Grand Total 2,315 12

Formula / click path

Total Sales  := SUM(Orders[Amount])
Order Count  := COUNTROWS(Orders)
Customers    := DISTINCTCOUNT(Orders[CustomerID])
Pune Sales   := CALCULATE([Total Sales], Stores[City] = "Pune")
Pune Share   := DIVIDE([Pune Sales], [Total Sales])

Do it step by step:

  1. Click Power Pivot › Calculations › Measures › New Measure….
  2. Set Table name to Orders and Measure name to Total Sales.
  3. In Formula type =SUM(Orders[Amount]), set Category to Currency, and click OK.
  4. Repeat for Order Count with =COUNTROWS(Orders) and Customers with =DISTINCTCOUNT(Orders[CustomerID]).
  5. Create Pune Sales with =CALCULATE([Total Sales], Stores[City] = "Pune").
  6. Click Insert › PivotTable › From Data Model, put Stores[City] in Rows and the three measures in Values.
  7. Put Pune Sales alone in a cell-sized PivotTable: it shows the Pune total.

What each function does (with this data)

Measure Formula Result
Total Sales =SUM(Orders[Amount]) ₹2,315
Order Count =COUNTROWS(Orders) 12
Customers =DISTINCTCOUNT(Orders[CustomerID]) 6
Pune Sales =CALCULATE([Total Sales], Stores[City] = "Pune") ₹940
Blinkit Sales =CALCULATE([Total Sales], Stores[Platform] = "Blinkit") ₹1,610
Pune Share =DIVIDE([Pune Sales], [Total Sales]) 40.6%

CALCULATE changes the filter. In a PivotTable by City, Pune Sales shows ₹940 on every city row, because CALCULATE replaces the City filter with "Pune". That is useful for "share of Pune" style measures and confusing if you expect it to follow the row.

Measure or calculated column? Use a calculated column (typed in a new column of the table in the Power Pivot window, such as =Orders[Qty]*RELATED(Products[Price])) only when you need a value per row to slice by. For totals, counts and ratios, always use a measure. DAX is explained further in the Power BI course: DAX: Data Analysis Expressions.

Practice task (try it before you open the answer):

  1. Create Total Sales, Order Count and Customers.
  2. Create Blinkit Sales with CALCULATE.
  3. Build a PivotTable by Stores[Platform] with Total Sales and Customers.
Answer

Blinkit Sales = ₹1,610. By Platform: Blinkit ₹1,610, Amazon Now ₹705, Grand Total ₹2,315. Customers by Platform: Amazon Now 2, Blinkit 4; Grand Total 6.

Ravindra Bagale's Tip

Name measures the way a manager would say them: "Total Sales", "Customers", "Pune Share". Avoid "Sum of Amount 2". Good names make the PivotTable read like a report.