Mastering DAX in Power BI – The Ultimate Guide for Data Analysts - PART II : Deep Dive into DAX - Calculations & Aggregations
Updated: Aug 10, 2025
In the first part of our DAX series, we understood the basics of DAX — what it is, why we use it in Power BI, how to start with Measures and Calculated Columns, and how filters shape our results. Now, we’re ready to deep dive into the first core DAX category: Calculations & Aggregations. This is where DAX truly comes alive — turning raw transactional data into totals, averages, counts, percentages, rankings, and more so your reports answer real business questions.
When Do We Know to Use DAX Calculations & Aggregations?
Whenever your report needs to summarize, combine, or calculate values beyond what’s stored in your raw dataset, that’s a clear signal you’ll need DAX Calculation and Aggregation functions.
Here are the typical questions where they come into play:
What is the total sales, profit, or revenue for a given period?
How many customers, orders, or products were sold?
What is the average sales per customer, order, or product?
Which is the highest or lowest selling product, region, or category?
What is the percentage growth or profit margin?
If your report asks any of these questions (or similar ones), you’ll know you’re working with the Calculation & Aggregation category of DAX. It’s called Calculation & Aggregation because while many cases are just aggregation some functions perform calculations first before aggregating the results.
Most Common Calculation & Aggregation DAX Functions
SUM() – Sum of values in a column.
SUMX() – Row-by-row expression, then sum.
AVERAGE() – Average of a column.
AVERAGEX() – Row-by-row expression, then average.
COUNT() – Counts non-blank values.
COUNTA() – Counts non-empty values (including text).
COUNTROWS() – Counts rows in a table.
COUNTBLANK() – Counts blank values in a column.
DISTINCTCOUNT() – Counts unique values.
MAX() – Returns the highest value in a column.
MAXX() – Row-by-row max from an expression.
MIN() – Returns the lowest value in a column.
MINX() – Row-by-row min from an expression.
DIVIDE() – Safely divides two numbers (handles zero).
PRODUCT() – Multiplies all values in a column.
PRODUCTX() – Row-by-row product of an expression.
PERCENTILE.INC() – Inclusive percentile.
PERCENTILE.EXC() – Exclusive percentile.
MEDIAN() – Median of a column.
MEDIANX() – Row-by-row median.
VAR.P() – Population variance.
VAR.S() – Sample variance.
VARX.P() – Row-by-row population variance.
VARX.S() – Row-by-row sample variance.
STDEV.P() – Population standard deviation.
STDEV.S() – Sample standard deviation.
STDEVX.P() – Row-by-row population standard deviation.
STDEVX.S() – Row-by-row sample standard deviation.
FIRSTNONBLANK() – Returns the first non-blank value.
LASTNONBLANK() – Returns the last non-blank value.
They fall into 5 sub-groups:
Summation & Multiplication
Averages & Medians
Counting
Min/Max
Statistical Metrics
Summation & Multiplication | Averages & Medians | Counting | Min/Max | Statistical Metrics |
SUM() | AVERAGE() | COUNT() | MIN() | VAR.P() |
SUMX() | AVERAGEX() | COUNTA() | MINX() | VAR.S() |
PRODUCT() | MEDIAN() | COUNTROWS() | MAX() | VARX.P() |
PRODUCTX() | MEDIANX() | COUNTBLANK() | MAXX() | VARX.S() |
DIVIDE() | DISTINCTCOUNT() | STDEV.P() | ||
STDEV.S() | ||||
STDEVX.P() | ||||
STDEVX.S() | ||||
PERCENTILE.EXC() | ||||
FIRSTNONBLANK() | ||||
LASTNONBLANK() |
Summation & Multiplication
These functions help add, multiply or divide values across rows to generate totals or combined results, transforming raw transactional numbers into meaningful KPIs.
Functions in this category include: SUM(), SUMX(), PRODUCT(), PRODUCTX(), and DIVIDE().
These are best used as Measures in most cases, not Calculated Columns. Why? Because totals, averages, and similar metrics are dynamic — they should change based on filters (like by region, date, or product). Populating the same “Total Sales” value in every row as a Calculated Column adds no analytical value and bloats the model. Though there are exceptions — dynamic Measures remain the preferred choice.
SUM()
The SUM() function adds up all the numeric values in a column. For example, to find the total MSS score of all patients, we create this Measure:
Total MSS Score = SUM(Patient_Task_Timeseries_Details[MSS_Score])Here:
Patient_Task_Timeseries_Details is the table with patient data.
MSS_Score is the column storing each patient’s score.
SUM() aggregates all those scores into one total.
Navigation to create this as a Measure in Power BI:
Go to the Fields pane (where all your tables are listed).
Right-click on Patient_Task_Timeseries_Details.
Select New Measure.
Enter the formula and press Enter.
The measure will now appear under the table, ready to use in visuals.
SUMX()
The SUMX() function iterates row by row over a table, calculates an expression for each row, and then sums the results. For example, if each patient’s MSS Score needs to be multiplied by 3 before summing, we create this measure:
Weighted MSS Score = SUMX(Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score] * 3)Here:
Patient_Task_Timeseries_Details is the table.
For each row, MSS_Score * 3 is calculated.
SUMX() then adds up all those row-level results.
A Common Confusion - Why it’s a Measure (not a Calculated Column):
All functions ending with X work row by row, but after the row-level calculations, they return a single final result that updates dynamically with any filters (like month or patient group). Calculated Columns are only used when you need static, stored values (that remain the same regardless of filters).
Navigation to create this as a Measure in Power BI:
In the Fields pane, right-click Patient_Task_Timeseries_Details.
Select New Measure.
Enter the formula above and press Enter.
The Measure appears under the table, ready for visuals.
PRODUCT()
The PRODUCT() function multiplies all numeric values in a column to return a single cumulative product. For example, if you want the product of all MSS Scores across patients, you can create this Measure:
Total MSS Product = PRODUCT(Patient_Task_Timeseries_Details[MSS_Score])Here:
Patient_Task_Timeseries_Details is the table.
MSS_Score is the column for each patient’s score.
PRODUCT() multiplies every score together into a single number.
Navigation to create this as a Measure in Power BI:
In the Fields pane, right-click on Patient_Task_Timeseries_Details.
Select New Measure.
Paste the formula above and press Enter.
The Measure will now be available for visuals.
PRODUCTX()
The PRODUCTX() function iterates row by row over a table, evaluates an expression for each row, and then multiplies all those results together into a single product. For example, if each patient’s MSS Score needs to be multiplied by 2 first and then all results multiplied together, we create this measure:
Weighted MSS Product = PRODUCTX(Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score] * 2)Here:
Patient_Task_Timeseries_Details is the table.
For each row, MSS_Score * 2 is calculated.
PRODUCTX() multiplies all those row-level results into one final product value.
Navigation to create this as a Measure in Power BI:
In the Fields pane, right-click on the table Patient_Task_Timeseries_Details.
Select New Measure.
Paste the formula and press Enter.
The Measure will now appear under the table, ready to use in visuals.
DIVIDE()
The DIVIDE() function performs division while avoiding divide-by-zero errors, which makes it safer than using the / operator. For example, imagine we have 500 patients, and we want to calculate the average MSS Score - We can do this using:
Average MSS Per Patient = DIVIDE(SUM(Patient_Task_Timeseries_Details[MSS_Score]), 500)Here:
SUM(Patient_Task_Timeseries_Details[MSS_Score]) adds up all patient MSS scores
DIVIDE() divides the total MSS Score by 500, and handles cases where there are zero patients, preventing errors.
Navigation to create this as a Measure in Power BI:
In the Fields pane, right-click on Patient_Task_Timeseries_Details.
Select New Measure.
Enter the formula above and press Enter.
The Measure can now be used in visuals, dynamically recalculating if filters (like region, time, or patient group) are applied.
Note on Handling Blanks (NULL Values):
All functions in this category — SUM, SUMX, PRODUCT, PRODUCTX, and DIVIDE — automatically skip blank (NULL) values when performing calculations.
SUM, SUMX, PRODUCT, and PRODUCTX only process valid numeric values and ignore blanks.
SUM() and SUMX() return 0 when all values are blank.
PRODUCT() and PRODUCTX() return BLANK() when all values are blank.
DIVIDE additionally allows you to handle zero denominators by specifying a default result (like 0) as a third parameter:
Average MSS Per Patient = DIVIDE(SUM(Patient_Task_Timeseries_Details[MSS_Score]), Total Patients, 0)This ensures your measures don’t break or show errors if there are no patients, making DIVIDE() the preferred choice over the / operator.
Hope by now, we’re clear on how to create new Measures for our DAX formulas. With this foundation, let’s move into the next category of DAX functions and explore how they help us unlock deeper insights.
Averages & Medians
These functions calculate average and middle-point values across rows, helping summarize datasets into representative figures (like typical scores or central tendencies).They simplify trend analysis by smoothing raw data into easy-to-compare metrics.
Functions in this category include: AVERAGE(), AVERAGEX(), MEDIAN() and MEDIANX().
These are best used as Measures, not Calculated Columns, because averages and medians should dynamically adjust based on filters (such as date ranges, clinics, or patient groups).Storing the same “average” in every row as a Calculated Column offers no analytical value and can unnecessarily bloat the model, though rare exceptions may apply.
AVERAGE()
The AVERAGE() function returns the mean (average) of all numeric values in a column. It’s almost always created as a Measure because averages need to be dynamic, changing based on filters like region or month.
Average MSS Score = AVERAGE(Patient_Task_Timeseries_Details[MSS_Score])Here:
Patient_Task_Timeseries_Details[MSS_Score] contains each patient’s score.
DAX internally calculates the Average MSS as:
Total of MSS_Score across all rows
-------------------------------------------------
Number of non-blank MSS Scores (rows)The denominator is the count of all rows where MSS_Score has a value, automatically skipping blanks.
This Measure recalculates whenever you apply filters (e.g., only for a specific age group or clinic).
AVERAGEX()
The AVERAGEX() function iterates row by row, evaluates an expression for each row, and then averages the results. Even though it works row by row, it’s still a Measure, because the final result is a single dynamic value that reacts to filters, not a fixed per-row value.
Weighted Average MSS = AVERAGEX(Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score] * 2)Here:
Patient_Task_Timeseries_Details[MSS_Score] represents each patient’s individual score.
DAX computes the Weighted Average MSS using the formula:
Total of (MSS_Score × 2) across all rows
-------------------------------------------------
Number of non-blank MSS Scores (rows)
Only rows with a valid MSS_Score are counted in the denominator, so empty values are automatically excluded.
The result updates dynamically whenever filters are applied, such as restricting to certain age groups, time periods, or clinic locations.
MEDIAN()
The MEDIAN() function finds the middle value (50th percentile) from all numeric values in a column. It’s best as a Measure because medians typically need to adjust dynamically with filters rather than remain static per row.
Median MSS Score = MEDIAN(Patient_Task_Timeseries_Details[MSS_Score])This returns the median MSS score across all patients, updating based on any applied filters.
let’s make the Median function crystal clear with a real example. Suppose the Patient_Task_Timeseries_Details[MSS_Score] column has these 9 scores (odd number - ignoring blanks):
5, 7, 8, 6, 10, 4, 9, 6, 7Step 1 – Sort the scores in ascending order:
4, 5, 6, 6, 7, 7, 8, 9, 10Step 2 – Find the middle value:
There are 9 entries (an odd number).
The 5th value (middle one) is the median.
Median MSS Score = 7If there were 10 scores (even number - ignoring blanks), for example:
4, 5, 6, 6, 7, 7, 8, 9, 10, 12There’s no single middle value because there are 10 scores.
DAX takes the average of the two middle values (the 5th and 6th scores).
Median MSS Score = (7 + 7) / 2 = 7Key points:
Median is the middle score when all values are ordered.
Blanks are ignored.
Automatically recalculates based on any filters (like date, patient group, or clinic).
MEDIANX()
The MEDIANX() function iterates row by row, evaluates an expression for each row, and then returns the median of those calculated results. Like other X functions, it’s also a Measure — the final output is one dynamic value, not stored per row.
Weighted Median MSS = MEDIANX(Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score] * 3This finds the median of all MSS Scores after multiplying each score by 3, dynamically responding to filters.
Note on Handling Blanks (NULL Values):
All functions in this category — AVERAGE(), AVERAGEX(), MEDIAN(), and MEDIANX() — automatically skip blank (NULL) values when performing calculations.
AVERAGE() and AVERAGEX() only process valid numeric entries, ignoring blanks so that missing data doesn’t distort results.
MEDIAN() and MEDIANX() sort all non-blank values and find the middle value (or average the two middle values if there’s an even count).
Median MSS Score = MEDIANX(Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score])If all rows are blank or no data remains after filters, these functions return BLANK() instead of throwing errors, keeping reports clean.
Counting
These functions determine how many values, rows, or unique entries exist in a dataset, helping identify volumes, detect missing data, and create totals for reporting.
Functions in this category include: COUNT(), COUNTA(), COUNTAX(), COUNTROWS(), and DISTINCTCOUNT().
Like averages, these are better as Measures, so counts recalculate automatically when filters (like region, time, or demographic) are applied. Static Calculated Columns often add little value and inflate the data model unnecessarily.
COUNT()
The COUNT() function counts all numeric, non-blank values in a column. It’s almost always created as a Measure because counts must update dynamically when filters (like date or region) are applied.
Count of Scores = COUNT(Patient_Task_Timeseries_Details[MSS_Score])Here:
Patient_Task_Timeseries_Details[MSS_Score] contains each patient’s score.
COUNT() only considers numeric values, skipping blanks and text automatically.
DAX internally calculates:
Number of numeric MSS_Score entries (non-blank , numeric only)This Measure recalculates whenever filters (e.g., by age group or clinic) are applied.
COUNTA()
The COUNTA() function counts all non-blank values, including text, numbers, and dates. Usually used as a Measure, except in rare cases for tagging rows in Calculated Columns.
Count of All Values = COUNTA(Patient_Task_Timeseries_Details[PatientID])Here:
Patient_Task_Timeseries_Details[PatientID] contains unique patient identifiers.
COUNTA() counts any row where the column has a value (not null), regardless of data type.
DAX internally calculates:
Number of rows where PatientID is not blank (any type)Dynamic filtering applies automatically.
COUNTROWS()
The COUNTROWS() function counts every row in a table or table expression, regardless of whether the columns inside that row are blank. Always used as a Measure when tied to filters.
Total Patients = COUNTROWS(Patient_Task_Timeseries_Details)Here:
Counts every row in Patient_Task_Timeseries_Details that survives current filters (e.g., specific clinic or date).
DAX internally calculates:
Total rows in the filtered Patient_Task_Timeseries_Details tableCOUNTBLANK()
The COUNTBLANK() function counts how many rows in a column are blank (NULL). Most often used as a Measure to track missing data.
Missing Scores = COUNTBLANK(Patient_Task_Timeseries_Details[MSS_Score])Here:
Patient_Task_Timeseries_Details[MSS_Score] contains each score.
COUNTBLANK() counts only the rows where the score is blank.
DAX internally calculates:
Number of MSS_Score rows that are blank (NULL)DISTINCTCOUNT()
The DISTINCTCOUNT() function counts unique, non-blank values in a column. Always created as a Measure for dynamic aggregation.
Unique Patients = DISTINCTCOUNT(Patient_Task_Timeseries_Details[PatientID])Here:
Patient_Task_Timeseries_Details[PatientID] stores patient identifiers.
DISTINCTCOUNT() excludes blanks and duplicates, returning the number of unique IDs.
DAX internally calculates:
Number of unique PatientID values (excluding blanks)Note on Handling Blanks (NULL Values)
All counting functions — COUNT(), COUNTA(), COUNTAX(), COUNTROWS(), COUNTBLANK(), and DISTINCTCOUNT() — handle blanks differently:
COUNT() only counts numeric, non-blank values and skips blanks automatically.
COUNTA() counts all non-blank values (numbers, text, and dates), ignoring blanks.
COUNTAX() works like COUNTA() but on an expression evaluated row by row, skipping blanks.
COUNTROWS() counts rows in a table (it does not care about blanks in specific columns).
COUNTBLANK() explicitly counts only blank (NULL) rows in a column.
DISTINCTCOUNT() excludes blanks and returns a count of unique, non-blank values.
If a table or column has only blanks after filters, these functions return 0 (except COUNTBLANK(), which returns the blank count), keeping results valid without errors.
Min/Max
These functions identify the smallest or largest values within a dataset, helping highlight ranges, detect outliers, and track minimum or maximum KPIs for analysis.
Functions in this category include: MIN(), MINX(), MAX(), and MAXX().
They are best used as Measures, because minimums and maximums should dynamically adjust when filters (like clinic, region, or date range) are applied.Creating static Calculated Columns for these values usually adds no analytical value and can inflate the data model unnecessarily, though there are rare row-level use cases.
MIN()
The MIN() function returns the smallest numeric value in a column. It is typically created as a Measure so the minimum adjusts dynamically when filters (like date or clinic) are applied.
Lowest MSS Score = MIN(Patient_Task_Timeseries_Details[MSS_Score])Here:
Patient_Task_Timeseries_Details[MSS_Score] contains each patient’s score.
MIN() evaluates all non-blank scores and returns the lowest value.
DAX internally calculates:
The single smallest MSS_Score across all non-blank rows (after filters)MINX()
The MINX() function evaluates an expression row by row for a table or table expression, then returns the smallest result. Always used as a Measure when the minimum is based on a calculated value.
Lowest Weighted Score = MINX( Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score] * 2 )Here:
Iterates through each row, calculates MSS_Score * 2, and returns the smallest resulting value.
DAX internally calculates:
Minimum of (MSS_Score × 2) across all rows (ignoring blanks)MAX()
The MAX() function returns the largest numeric value in a column. Usually defined as a Measure to remain dynamic under filters.
Highest MSS Score = MAX(Patient_Task_Timeseries_Details[MSS_Score])Here:
Patient_Task_Timeseries_Details[MSS_Score] holds each score.
MAX() finds the highest non-blank score.
DAX internally calculates:
The single largest MSS_Score across all non-blank rows (after filters)MAXX()
The MAXX() function evaluates an expression row by row for a table or table expression, then returns the largest result. It’s generally used as a Measure to calculate maximums based on formulas.
Highest Weighted Score = MAXX( Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score] * 2 )Here:
Iterates through each row, calculates MSS_Score * 2, and returns the largest value found.
DAX internally calculates:
Maximum of (MSS_Score × 2) across all rows (ignoring blanks)Note on Handling Blanks (NULL Values)
All Min/Max functions (MIN(), MINX(), MAX(), MAXX()) automatically ignore blank (NULL) values during calculations.If all rows are blank, they return BLANK(), not zero or an error.This ensures results stay clean and visuals don’t break when no valid data exists for the applied filters.
Statistical Metrics
These functions calculate variability, spread, and distribution within a dataset, helping analysts measure how data points deviate from the mean or where specific percentiles fall. They’re essential for understanding data consistency, outliers, and statistical significance in reports.
Functions in this category include: VAR.P(), VAR.S(), VARX.P(), VARX.S(),STDEV.P(), STDEV.S(), STDEVX.P(), STDEVX.S(),PERCENTILE.INC(), PERCENTILE.EXC(), FIRSTNONBLANK(), and LASTNONBLANK().
These are best created as Measures, since variance, standard deviation, percentiles, and first/last values should dynamically recalculate based on filters (such as time, region, or category).Storing these as Calculated Columns usually adds no analytical value and can unnecessarily bloat the data model, except in rare row-level cases.
VAR.P()
The VAR.P() function calculates the population variance for a column, measuring how much the values deviate from the mean for the entire dataset (population).It’s almost always used as a Measure so the variance dynamically changes with filters (e.g., region or time).
Population Variance (MSS) = VAR.P(Patient_Task_Timeseries_Details[MSS_Score])Here:
Patient_Task_Timeseries_Details[MSS_Score] contains each patient’s score.
DAX calculates the average squared difference of each score from the mean. If all rows are blank, the result is BLANK(), not 0.
Example:
Patient ID | MSS_Score |
P1 | 2 |
P2 | 4 |
P3 | 6 |
P4 | 4 |
P5 | 4 |
Step-by-Step Calculation:
Find the Mean (Average):
Mean = (2 + 4 + 6 + 4 + 4) / 5 = 20 / 5 = 4
Find the Squared Difference from the Mean for each value:
MSS_Score | Deviation from Mean | Squared Difference |
2 | 2 - 4 = -2 | (-2)² = 4 |
4 | 4 - 4 = 0 | 0² = 0 |
6 | 6 - 4 = 2 | 2² = 4 |
4 | 4 - 4 = 0 | 0² = 0 |
4 | 4 - 4 = 0 | 0² = 0 |
Sum of Squared Difference:
4+0+4+0+0=8
Divide by Total Count (Population):
Since this is population variance, we divide by n = 5:
VAR.P = 8 / 5 = 1.6
Final Result:
VAR.P(Patient_Task_Timeseries_Details[MSS_Score]) = 1.6So what this means? The average squared difference between each MSS score and the mean MSS score is 1.6 units².
Real-Time Example: Tracking Patient Recovery Scores
Scenario:
A hospital is monitoring the MSS (Mobility Support Score) of all patients in a rehab program. They want to understand how consistent or varied the recovery progress is across the patient population.
What They Do:
They collect MSS scores for each patient after 30 days of rehab.
They use VAR.P ( MSS_Score ) in Power BI to calculate the population variance.
Interpretation:
If VAR.P = 0.5 → Most patients are improving similarly (scores are close to the average).
If VAR.P = 10 → Patients are recovering at very different rates (some fast, some slow).
Why Use VAR.P() Here?
Because they want to measure the spread of scores across the entire group.
It helps doctors decide if the rehab program is working consistently or if it's effective only for certain patients.
VAR.P() tells how much variation exists across the full patient group — it’s used when you want to see consistency or inconsistency in performance, recovery, test results, etc.
VAR.S()
The VAR.S() function calculates the sample variance for a column, assuming the data represents a sample rather than the whole population.Always a Measure for dynamic recalculation.
Sample Variance (MSS) = VAR.S(Patient_Task_Timeseries_Details[MSS_Score])Here:
Works like VAR.P() but divides by (N – 1) in the step 4 above, accounting for sample bias.
Skips blanks automatically; returns BLANK() if no valid rows remain.
Real-Time Healthcare Use Case:
A healthcare researcher is studying a sample of 100 patients from a larger population to understand variation in mobility recovery scores (MSS) post-surgery. Since she doesn't have data from all patients in the population, she uses VAR.S() instead of VAR.P() to reduce bias in variance estimation.
VARX.P()
The VARX.P() function evaluates an expression row by row across a table, then calculates population variance of those results. Use it when variance is based on calculated values.
Population Variance (Weighted) = VARX.P( Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score] * 2 )Here:
Calculates MSS_Score × 2 for each row, then computes variance for the population.
Skips blanks; returns BLANK() if all results are blank.
VARX.S()
The VARX.S() function calculates sample variance for an expression evaluated across rows. Best as a Measure.
Sample Variance (Weighted) = VARX.S( Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score] * 2 )Same as VARX.P(), but divides by (N – 1) for sample bias.
STDEV.P()
We use VAR.P() to know how spread out values are (in squared units). But we need to use STDEV.P() to know how far values are from the mean in actual units. It shows how much values typically differ from the mean, in the original unit (not squared). It is commonly used as a Measure in Power BI for dynamic filtering by time, region, or group.
Population StdDev (MSS) = STDEV.P(Patient_Task_Timeseries_Details[MSS_Score])Here:
Patient_Task_Timeseries_Details[MSS_Score] contains each patient’s score.
Skips blanks; returns BLANK() if all rows are blank.
Example:
Patient ID | MSS_Score |
P1 | 2 |
P2 | 4 |
P3 | 6 |
P4 | 4 |
P5 | 4 |
Step-by-Step Calculation:
1. Find the Mean (Average)
Mean = (2 + 4 + 6 + 4 + 4) / 5 = 20 / 5 = 4
2. Calculate Squared Differences from the Mean
MSS_Score | Deviation from Mean | Squared Difference |
2 | -2 | 4 |
4 | 0 | 0 |
6 | 2 | 4 |
4 | 0 | 0 |
4 | 0 | 0 |
3. Sum of Squared Differences
4+0+4+0+0=8
4. Divide by Total Count (N = 5)
Variance=8/5=1.6
5. Take Square Root to Get Standard Deviation
STDEV.P = √1.6 ≈ 1.26
Final Result:
STDEV.P(Patient_Task_Timeseries_Details[MSS_Score]) ≈ 1.26So What This Means?. The standard deviation of MSS scores across all patients is 1.26, meaning that on average, scores differ from the mean by about 1.26 units. This gives a real-world sense of how much variation exists — in the same unit as the original data.
Real-Time Healthcare Example: Measuring Consistency in Recovery
Scenario:
A hospital wants to understand how consistently patients are recovering using the Mobility Support Score (MSS).
Instead of just knowing the spread (variance), they want to know the average deviation in actual units, so they use STDEV.P().
What They Do:
They calculate STDEV.P(MSS_Score) for all patients in the rehab program.
The goal: understand how far MSS scores are typically from the average score.
Interpretation:
If STDEV.P = 0.3 → Most patients are recovering very consistently (low deviation).
If STDEV.P = 3.5 → There’s a lot of inconsistency in recovery progress (some much faster/slower than others).
Why Use STDEV.P() Here?
Doctors want to interpret the spread in real-world units (MSS scale), not squared numbers.
It helps assess if patients are improving similarly or if some groups may need extra care or new treatments.
Can we calculate standard deviation without squaring the differences? No, we cannot skip squaring.
Why Squaring Is Needed:
Standard deviation is based on variance, and variance is defined as the average of the squared differences from the mean. We square the differences because:
It avoids canceling out positive and negative values. Without squaring, deviations like -2 and +2 would cancel each other out (sum = 0), which hides the real spread.
It gives more weight to larger deviations.This is important to show that outliers impact the spread more.
Then we take the square root of variance to bring it back to the original units — this gives us standard deviation.
Standard deviation is the square root of the average of squared differences from the mean — so squaring is required.
STDEV.S()
The STDEV.S() function calculates the sample standard deviation, assuming the dataset is a sample. Always a Measure.
Sample StdDev (MSS) = STDEV.S(Patient_Task_Timeseries_Details[MSS_Score])Divides by (N – 1) in step 4 above to correct for sample bias.
STDEVX.P()
The STDEVX.P() function evaluates an expression for each row and computes the population standard deviation of those results.
Population StdDev (Weighted) = STDEVX.P( Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score] * 2 )Like VARX.P() but returns the standard deviation (square root).
STDEVX.S()
The STDEVX.S() function computes sample standard deviation for an expression across rows.
Sample StdDev (Weighted) = STDEVX.S( Patient_Task_Timeseries_Details, Patient_Task_Timeseries_Details[MSS_Score] * 2 )Divides by (N – 1) for unbiased sample results.
Variance → Tells you how spread out your numbers are from the average, but the result is in square units, so it’s harder to explain directly. In business: Helps analysts detect how consistent or inconsistent results are. For example, in sales data, a high variance means sales vary a lot month to month, which could indicate unstable demand.
Standard Deviation → Measures the same spread but converts it back to the original unit, making it easier to understand in real life. In business: Lets you say things like, “On average, each store’s monthly sales are about $1,200 away from the average,” which is more meaningful for decision-making.
Example:
Variance says: “Spread is 1.6 (units²)” — useful for statistical calculations.
Standard Deviation says: “On average, each score is about 1.26 units away from the mean” — easier to communicate to stakeholders.
In short: Variance = technical measure of spread. Standard Deviation = business-friendly measure of spread.
Population Variance (VAR.P)
Measures how spread out the data is for the entire population. Result is in “square units” so it’s more technical.
Sample Business Questions:
What is the population variance of delivery times for all orders last month?
How much variability exists in patient wait times for an entire hospital system?
For all employees, how spread out are their yearly bonus amounts from the average?
How much variation is there in production output across all machines in the factory?
For all students in a school, what’s the variance in exam scores?
Sample Variance (VAR.S)
Measures spread for a sample (subset) of data — used when you don’t have full population data. Divides by n-1 instead of n.
Sample Business Questions:
Based on a random sample of 100 customers, what is the variance in their monthly spending?
For a sample of products, how much variability is there in weight?
How spread out are the sales amounts in a test market before launching nationwide?
For a sample of employees, what’s the variance in hours worked per week?
For a sample of patient visits, how variable are the treatment costs?
Population Standard Deviation (STDEV.P)
Same as population variance but converted back to original units — easier to explain to non-technical audiences.
Sample Business Questions:
On average, how far is each patient’s wait time from the overall hospital average?
How consistent are delivery times for all shipments?
For all employees, what’s the average difference between each person’s salary and the mean salary?
How much variation is there in electricity usage for all households in the city?
For all branches, what’s the standard deviation in monthly revenue?
Sample Standard Deviation (STDEV.S)
Same as sample variance but in original units — best for explaining spread in sampled data.
Sample Business Questions:
On average, how far is each customer’s spending from the mean in our sample of 100?
In our trial run of a new delivery route, how consistent were the delivery times?
For a sample of machines, what’s the average deviation in production output from the mean?
For our sample survey responses, how spread out are the satisfaction scores?
In a pilot study, what’s the standard deviation in weight loss among participants?
The PERCENTILE.INC() function returns the k-th percentile of values in a column, including the start and end points (0th and 100th percentiles allowed).Always used as a Measure.
90th Percentile Score = PERCENTILE.INC( Patient_Task_Timeseries_Details[MSS_Score], 0.9 )Here:
Returns the score below which 90% of scores fall.
Skips blanks; returns BLANK() if no data.
Example Data: MSS Scores
Patient ID | MSS_Score |
P1 | 2 |
P2 | 4 |
P3 | 6 |
P4 | 4 |
P5 | 8 |
Step-by-Step Calculation: Find the 75th Percentile
Percentile_75 = PERCENTILE.INC(Patient_Task_Timeseries_Details[MSS_Score], 0.75)1. Arrange scores in ascending order:
2, 4, 4, 6, 8
2. Determine position of the percentile:
Position = (n - 1) * k + 1
n = 5, k = 0.75
Position = (5 - 1) * 0.75 + 1
Position = 4 * 0.75 + 1 = 3 + 1 = 4th value3. Locate the value:
4th value in the sorted list is 6.
4. Final result: PERCENTILE.INC(...) = 6
Real-Time Healthcare Example
Scenario:
A hospital wants to know the 90th percentile of patient MSS scores after a rehab program to identify the top-performing patients.
What They Do:
They use PERCENTILE.INC (MSS_Score, 0.9) in Power BI.
If the result is 7.2, it means 10% of patients scored above 7.2 and 90% scored at or below it.
Sometimes, when we calculate a percentile, the position we get is not a whole number (like in the case above). This means the exact percentile lies between two values in our sorted list. In that case, we use interpolation
Why interpolate? When the percentile position isn’t an integer, the exact percentile falls between two data points. We take a proportional value between them so percentiles change smoothly (not in jumps).
Formula: If sorted values are …, L (lower rank), U (upper rank), and fraction = decimal part of the position,
Percentile = L + (U − L) × fraction
Example (data = 2, 4, 4, 6, 8; k = 0.9 with PERCENTILE.INC):
Sort → 2, 4, 4, 6, 8 (n = 5)
Position = (n−1) * k +1 = 4×0.9 +1 = 4.6
L = 4th value = 6, U = 5th value = 8, fraction = 0.6
Result = 6 + (8−6) × 0.6 = 6 + 1.2 = 7.2
Answer: PERCENTILE.INC(MSS_Score, 0.9) for {2,4,4,6,8} = 7.2.
Why use PERCENTILE.INC here?
Includes the full score range from lowest to highest.
Useful when edge values (min & max) are significant for business insights.
PERCENTILE.EXC()
The PERCENTILE.EXC() function returns the k-th percentile but excludes the endpoints (cannot compute 0th or 100th percentile).
90th Percentile (Exclusive) = PERCENTILE.EXC( Patient_Task_Timeseries_Details[MSS_Score], 0.9 )Used for more conservative statistical analysis.
Example Data: MSS Scores
Patient ID | MSS_Score |
P1 | 2 |
P2 | 4 |
P3 | 6 |
P4 | 4 |
P5 | 8 |
Step-by-Step Calculation:
Percentile_75_Exc = PERCENTILE.EXC(Patient_Task_Timeseries_Details[MSS_Score], 0.75)1. Arrange scores in ascending order:
2, 4, 4, 6, 8
2. Determine position of the percentile:
Position = (n + 1) * k
n = 5, k = 0.75
Position = (5 + 1) * 0.75 = 6 * 0.75 = 4.5PERCENTILE.INC uses Position = (n - 1) * k + 1 because it includes both the first (0th percentile) and last (100th percentile) values in the range.
PERCENTILE.EXC uses Position = (n + 1) * k because it excludes the extreme minimum and maximum, so the formula shifts the scaling accordingly.
3. Locate the value:
Position 4.5 means halfway between the 4th value (6) and 5th value (8).
Interpolation: 6 + (8 - 6) * 0.5 = 6 + 1 = 7.
4. Final result:
PERCENTILE.EXC(...) = 7
Real-Time Healthcare Example
Scenario:
A rehab center wants the 75th percentile of MSS scores excluding extreme min & max values, to focus on typical patient performance.
What they do :
Use PERCENTILE.EXC (MSS_Score, 0.75) in Power BI.
If the result is 7, it means 25% of patients scored above 7 but the calculation ignores the absolute highest and lowest scores.
This helps avoid skew from extreme outliers.
Why use PERCENTILE.EXC here?
Excludes extreme ends of data for a more representative percentile.
Better for performance benchmarks that shouldn’t be impacted by outliers.
Sample Questions for PERCENTILE.INC/PERCENTILE.EXC
Identify patients in the top 5% risk percentile for preventive care.
Find the 75th percentile sales number to set targets.
Flag bottom 10% product quality scores for investigation.
Highlight top 1% student scores for awards.
Identify bottom 25% customer satisfaction ratings for improvement.
k Value Table for Easy Reference
Here’s the table updated with top, bottom, and mid percentile cases:
Business Case | Description | k value (for PERCENTILE functions) |
Top X% | Value above which the top X% of data lies | 1 - (X / 100) |
Bottom X% | Value below which the bottom X% of data lies | X / 100 |
Mid / Specific % | Value at which Y% of data lies at or below | Y / 100 |
Examples:
Top 5% risk percentile → k = 1 - 0.05 = 0.95
Bottom 10% quality scores → k = 0.10
75th percentile sales goal → k = 0.75
FIRSTNONBLANK()
The FIRSTNONBLANK() function returns the first value in a column (based on the current sort order) that is not blank, along with the associated expression context.Common in time intelligence.
First Score = FIRSTNONBLANK( Patient_Task_Timeseries_Details[MSS_Score], 1 )Skips blanks automatically; returns BLANK() if all rows are blank.
LASTNONBLANK()
The LASTNONBLANK() function returns the last value in a column (based on the current sort order) that is not blank, along with the associated expression context.
Last Score = LASTNONBLANK( Patient_Task_Timeseries_Details[MSS_Score], 1 )Skips blanks; returns BLANK() if no non-blank values exist.
Note on Handling Blanks (NULL Values)
All statistical functions (VAR.*, VARX.*, STDEV.*, STDEVX.*, PERCENTILE.INC/EXC, FIRSTNONBLANK(), LASTNONBLANK()) automatically skip blanks during calculations. If no valid rows remain after filtering, they return BLANK() instead of errors or zeros, ensuring visuals stay clean.
DAX Functions - Blank Return Behavior listing all these DAX functions by category, showing what each function returns when all values are blank (NULL).
Summation & Multiplication | Averages & Medians | Counting | Min/Max | Statistical Metrics |
SUM() - 0 | AVERAGE() - BLANK() | COUNT() - 0 | MIN() - BLANK() | VAR.P() - BLANK() |
SUMX() - 0 | AVERAGEX() - BLANK() | COUNTA() -0 | MINX() - BLANK() | VAR.S() - BLANK() |
PRODUCT() - BLANK() | MEDIAN() - BLANK() | COUNTROWS() - Row Count (includes blanks) | MAX() - BLANK() | VARX.P() - BLANK() |
PRODUCTX() - BLANK() | MEDIANX() - BLANK() | COUNTBLANK() - 0 | MAXX() - BLANK() | VARX.S() - BLANK() |
DIVIDE() - 0 (if default specified, else BLANK()) | DISTINCTCOUNT() - 0 | STDEV.P() - BLANK() | ||
STDEV.S() - BLANK() | ||||
STDEVX.P() - BLANK() | ||||
STDEVX.S() - BLANK() | ||||
PERCENTILE.INC() - BLANK() | ||||
PERCENTILE.EXC() - BLANK() | ||||
FIRSTNONBLANK() - BLANK() | ||||
LASTNONBLANK() - BLANK() |
Key Takeaways
Understand categories of DAX functions – Summation, Averages & Medians, Counting, Min/Max, and Statistical Metrics each serve distinct purposes.
Measures over Calculated Columns – Dynamic calculations should almost always be Measures so they adjust with filters and reduce model bloat.
Blank-handling matters – Functions differ in how they treat blanks (refer the table above).
Use the right variant – Functions like SUMX(), AVERAGEX(), MEDIANX(), VARX.*, and STDEVX.* let you calculate results row by row before aggregating, which is critical for weighted or calculated metrics.
Consistency drives clarity – Organizing functions with clear patterns (purpose, behavior, blank handling) makes DAX easier to understand and apply across real business scenarios.
Mastering DAX isn’t just about memorizing functions — it’s about understanding how and when to use them, how they handle blanks and context, and how to structure them as Measures for dynamic, efficient models. By grouping functions into clear categories and focusing on real-world behaviors, this blog gives analysts a reference they can rely on to write cleaner, faster, and more accurate DAX in Power BI.
Happy DAXing !!
