ExamFinch.com My Study

Study guide

5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups

Key Takeaways Explain This Topic AI

Core statistical DAX: AVERAGE, MEDIAN, MIN/MAX, STDEV.P/STDEV.S, VAR.P/VAR.S, PERCENTILE.INC/EXC, RANKX.

Semi-additive measures (balances, inventory, headcount) can be summed across most dimensions but NOT across time.

LASTNONBLANK/LASTDATE return the most recent value; FIRSTNONBLANK returns the opening value, for snapshot measures.

DIVIDE() is preferred over the / operator because it returns BLANK() (or an alternate) instead of erroring on divide-by-zero.

Calculation groups define time-intelligence patterns once and apply them to any measure via SELECTEDMEASURE().

Last updated: June 2026

Quick Answer: Use DIVIDE() instead of / for safe division. Semi-additive measures (balances, inventory) should not be summed across dates; use LASTNONBLANK or LASTDATE for the most recent value. Calculation groups let you define time-intelligence patterns (YTD, PY, YoY) once and apply them to any measure automatically.

Aggregation Functions

  • Function Description
  • SUM Adds all values
  • AVERAGE Arithmetic mean
  • MIN / MAX Smallest / largest value
  • COUNT Count numeric non-blanks
  • COUNTA Count non-blanks of any type
  • COUNTBLANK Count blank/null values
  • COUNTROWS Count rows in a table
  • DISTINCTCOUNT Count unique values
  • Advanced Statistical Functions
  • Function Description
  • MEDIAN Middle value when sorted
  • STDEV.P / STDEV.S Population / sample standard deviation
  • VAR.P / VAR.S Population / sample variance
  • PERCENTILE.INC / PERCENTILE.EXC Kth percentile, inclusive / exclusive
  • RANKX Rank values in a table

Choose the .S (sample) variants when your data is a sample of a larger population and .P (population) when it represents the entire population. Iterator versions (SUMX, AVERAGEX, COUNTX, MINX, MAXX) evaluate an expression per row, then aggregate. Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

Avg Order Profit = AVERAGEX(Sales, Sales[Revenue] - Sales[Cost])

The DIVIDE Function

DIVIDE(numerator, denominator [, alternateResult]) is safe division.

  • Operation / Operator DIVIDE()
  • 10 / 5 2 2
  • 10 / 0 Error/Infinity BLANK() (or alternate)
  • 0 / 0 Error/NaN BLANK() (or alternate) Profit Margin = DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]))
  • Profit Margin = DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]), 0)

Best Practice: Always use DIVIDE() in measures; it handles divide-by-zero gracefully and avoids runtime errors.

Semi-Additive Measures

  • Measure Type Additive Across Time? Example
  • Fully Additive Yes Revenue, Quantity, Cost
  • Semi-Additive No Account Balance, Inventory, Headcount
  • Non-Additive No (across any dimension) Ratios, Percentages

Summing monthly inventory snapshots (100 + 120 + 90 = 310) is meaningless; you want the last known value (90). Current Inventory =

CALCULATE(SUM(Inventory[Units]), LASTNONBLANK('Date'[Date], CALCULATE(COUNTROWS(Inventory))))

Ending Balance = CALCULATE(SUM(Accounts[Balance]), LASTDATE('Date'[Date]))

  • Opening Balance =
  • CALCULATE(SUM(Accounts[Balance]), FIRSTNONBLANK('Date'[Date], CALCULATE(COUNTROWS(Accounts))))

Note that semi-additive measures are still additive across non-time dimensions, summing balances across several accounts on the same date is correct.

Calculation Groups

Without calculation groups you must author a separate measure for every base x pattern combination. With 3 base measures and 5 patterns you get 15 measures; with 20 base measures you get 100. Calculation groups define the patterns once and apply them to any selected measure.

  • Calculation Item Logic
  • Current SELECTEDMEASURE()
  • YTD CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))
  • PY CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date]))
  • YoY SELECTEDMEASURE() - CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date]))
  • QTD CALCULATE(SELECTEDMEASURE(), DATESQTD('Date'[Date]))

SELECTEDMEASURE() is a placeholder for whichever measure the user puts in the visual, so any base measure (Revenue, Cost, Profit) can be viewed through any pattern selected from a slicer. Calculation groups are authored in Tabular Editor or directly in Power BI Desktop (Model view > New calculation group), and items are ordered with an ordinal to control display order.

Exam Tip: Know the purpose of calculation groups and recognize the scenario, dozens of repetitive time-intelligence measures, where they are the right solution.

On the Exam

The PL-300 tests using DIVIDE() over /, identifying semi-additive measures and handling them with LASTNONBLANK/LASTDATE, choosing SUM vs. SUMX, and the purpose of calculation groups.

Additivity Is a Property of the Measure, Not the Column

The exam wants you to classify a measure by how it may be aggregated. Fully additive values (revenue, quantity, cost) can be summed across every dimension, including time. Semi-additive values (account balance, inventory on hand, headcount) can be summed across non-time dimensions but not across time, summing three monthly balances invents money. Non-additive values (ratios, percentages, averages, distinct counts) cannot be summed across any dimension and must be recomputed from their components in each context.

Misclassifying a measure produces totals that look plausible but are wrong, which is why scenarios about "the total at the bottom of the matrix is too high" usually hinge on additivity.

Picking the Right Snapshot Function

For semi-additive snapshots, the choice of function depends on whether your snapshot rows are dense or sparse. LASTDATE returns the value at the latest date in context , which works when every period has a row. LASTNONBLANK (and FIRSTNONBLANK ) scan backward (or forward) for the most recent date where an expression is non-blank, which is safer when some periods are missing, for example a balance that is only recorded on business days. OPENINGBALANCE* and CLOSINGBALANCE* functions wrap these patterns for month, quarter, and year and are worth recognizing by name.

Sample vs. Population, and Inclusive vs. Exclusive

Two function-family distinctions appear regularly. Use STDEV.S / VAR.S (sample) when your rows are a sample drawn from a larger population and STDEV.P / VAR.P (population) when the rows are the entire population, the sample variants divide by n-1, the population variants by n. Similarly, PERCENTILE.INC includes the endpoints (0th and 100th percentile are valid) while PERCENTILE.EXC excludes them; Excel-compatible reports usually expect .INC . Knowing which variant a scenario calls for is a small but real source of exam points.