Skip to content

Calculations

Calculations are primarily done in the DAX layer.

Measures and Calculated Columns

1. Use Clear, Descriptive Names

  • Names should reflect business meaning.
  • Avoid unclear or technical abbreviations.
  • Use consistent naming patterns.

Good examples:

  • TotalSales
  • GrossMargin
  • CustomerCount

Bad examples:

  • TotalCount (too generic)
  • CustomerDetail (inconsistent casing)
  • LastPurchase (unclear context)

2. Always Use VAR in DAX for Clarity and Performance

  • Makes measures easier to read.
  • Prevents repeated calculations.
  • Greatly simplifies debugging.

Example:

VAR Revenue = SUM(Sales[Amount])
RETURN Revenue

3. Measures vs. Calculated Columns

Use a Measure When:

  • The result depends on filters or slicers.
  • The value must aggregate dynamically (SUM, COUNT, AVERAGE, etc.).
  • The result should change based on context.
  • Logic applies at report time.

Examples:

  • Total revenue
  • KPIs
  • Ratios

Use a Calculated Column When:

  • The value is fixed per row.
  • Needed for relationships.
  • Needed for Sort By Column.
  • Implements static business logic.

Examples:

  • Product category
  • Year-Month text
  • Custom grouping

Avoid Calculated Columns When:

  • Logic must be dynamic.
  • They increase model size unnecessarily.

Note: Calculated columns can actually improve performance because they are calculated at refresh time, whereas measures are calculated during report usage.

4. Use DIVIDE() Instead of the "/" Operator

  • Prevents divide-by-zero errors.
  • Ensures consistent behavior in totals.
  • Keeps visuals stable.

5. Keep Measures Small and Modular

  • Break long or complex logic into helper measures.
  • Improves readability and maintainability.
  • Encourages reuse across the model.

6. Keep Formatting Out of Core Measures

Avoid using FORMAT() in calculation logic—it converts numbers into text.

Prefer separate measures:

  • A numeric business measure
  • A formatted display measure

7. Organize Measures into Display Folders

Use display folders to structure your model:

  • Sales
  • Finance
  • KPIs
  • Time Intelligence

A clean model improves collaboration and long-term maintenance.

Power BI Calculation Types — Architecture & Governance

Aspect Calculated Columns Measures Calculation Groups Field Parameters Visual Calculations
Defined Where Data Model Data Model Data Model (Power BI Desktop / Tabular Editor) Data Model Inside a Visual
Evaluated When Refresh time Query time Query time Query time (structure) Render time
Scope Row-level Model-wide Model-wide Visual structure Single visual
Reusable ❌ ✅ ✅ ✅ ❌
Filter Aware ❌ ✅ ✅ ⚠️ (indirect) ⚠️ (visual only)
Uses DAX ✅ ✅ ✅ ⚠️ (metadata only) ❌ (visual formula language)
Uses SELECTEDMEASURE() ❌ ❌ ✅ ❌ ❌
Changes Business Meaning ⚠️ ✅ ✅ ❌ ❌
Defines KPIs ❌ ✅ ❌ ❌ ❌
Applies to Many Measures ❌ ❌ ✅ ❌ ❌
Time Intelligence ❌ ⚠️ (only if context is inherently row-level) ✅ ❌ ❌
Currency / Scenario Logic ❌ ⚠️ (not scalable) ✅ ❌ ❌
User Choice / UX Control ❌ ❌ ❌* ✅ ❌
Presentation Logic ❌ ❌ ⚠️ (formatting only) ❌ ✅
Affects Model Size ✅ ❌ ❌ ❌ ❌
Auditable / Governed ✅ ✅ ⚠️ (hidden semantic layer) ⚠️ ❌
Risk if Misused Medium Medium High Medium Low
Typical Use Cases Sorting, grouping, row attributes KPIs, totals, ratios YTD, LY, TTM, FX, Scenario KPI / UoM / Dimension selector % of row, visual deltas

Visual Calculations

Visual calculations are evaluated in the visual context and provide a simpler alternative to complex DAX measures for certain scenarios.

Best practices will be added as we gain more experience with this feature.

Field Parameters

Field parameters enable dynamic field selection in visuals, allowing users to switch between different measures or dimensions.

Best practices will be added as we establish patterns for effective parameter usage.

Calculation Groups

Calculation groups provide a way to reduce the number of redundant measures by applying common calculation patterns (e.g., time intelligence, currency conversion) across multiple measures.

Best practices will be documented as we develop standardized calculation group patterns.