Power BI Interview
Questions and DAX answers (2026)

Power BI is Microsoft's business intelligence platform for connecting to data, building models and publishing interactive reports and dashboards. It has four main components:

• Power BI Desktop — the authoring tool where you connect, model and build reports.
• Power BI Service — the cloud portal where reports are published, refreshed and shared.
• Power BI Mobile — apps for iOS and Android that display reports on phones and tablets.
• On-premises data gateway — a bridge that lets the cloud service refresh data from sources inside the company network.

Desktop is for building; Service is for sharing and refresh; Mobile is for viewing; the gateway keeps on-premises data in sync.

Power Query is the data preparation engine in Power BI. It connects to sources, cleans and transforms data, and loads the result into the model.

Every step you take in the Power Query Editor is recorded as a step in the Applied Steps pane and written as M code. That means the transformation is repeatable — the next time you refresh, the same steps run against the new data.

Use Power Query for renaming columns, changing data types, removing duplicates, splitting and merging columns, unpivoting wide tables, and filtering rows before they reach the model.

Most reports use Import because it is fastest. DirectQuery is chosen when freshness or data size forces it. Composite is used when a single model must mix the two.

A star schema has one central fact table surrounded by flat dimension tables. Each dimension is a single table with all the descriptive columns.

A snowflake schema normalises those dimensions into multiple related tables — for example, Product → Category → Department.

In Power BI, flatten snowflakes into star schemas in Power Query wherever practical. Simpler models mean faster measures and fewer mistakes.

There are three cardinality types:

• One-to-many (1:*) — the most common. One row in the dimension matches many rows in the fact table. Example: one Product row, many Sales rows.
• Many-to-one (*:1) — the same relationship from the fact side.
• One-to-one (1:1) — one row in each table. Rare, often used for splitting a wide table.
• Many-to-many (*:*) — supported in Power BI, but needs care. Usually handled by an intermediate bridge table.

Always try to model relationships as one-to-many from a dimension to a fact table. Many-to-many relationships often signal a modelling problem that should be fixed in Power Query.

Rule of thumb: use a measure for anything that aggregates. Use a calculated column only when the value must be sliced, filtered or used in a relationship.

Row context means "the current row". It exists inside a calculated column and inside iterator functions like SUMX and FILTER. In row context, you can read the value of the current row directly — Sales[Qty].

Filter context means "the filters applied to the data at the moment of evaluation". It comes from slicers, rows and columns in a visual, page filters and report filters, plus any filters added by CALCULATE.

Example: in a table visual by Year and Product, each cell evaluates the measure under a filter context of that Year and Product. The measure adds up only the rows that survive those filters.

CALCULATE is the most important DAX function. It evaluates an expression in a modified filter context.

Sales Red =
CALCULATE(
    [Total Sales],
    Product[Color] = "Red"
)

This measure always returns sales only for Red products, regardless of what colour is selected on the page. CALCULATE takes the existing filter context and applies additional filters.

It also triggers context transition — when CALCULATE is used inside a row context, the current row becomes a filter context. That is how measures inside SUMX or FILTER evaluate row by row.

A date table is a dedicated table that contains one row per date, with columns like Year, Quarter, Month, Week and Day. It is connected to the fact table through the date column.

Time intelligence functions like TOTALYTD, SAMEPERIODLASTYEAR and DATEADD require a proper date table. Without one, they return wrong results.

Mark the date table as a date table in the model (Table tools → Mark as date table) and ensure it has no gaps and covers all the dates in your fact table, plus a full year on each side.

Row-level security (RLS) restricts which rows a user can see in a report, based on their role. You define roles in Power BI Desktop, and assign users to those roles in the Power BI Service.

Two types:

• Static RLS — the filter is hard-coded. Example: a role "North" with the filter Region[Name] = "North".
• Dynamic RLS — the filter uses the logged-in user's identity. Example: Region[Email] = USERPRINCIPALNAME().

RLS filters apply to the whole model for that user. Test it in Desktop with "View as role" before publishing.

A slicer is a visual filter that lets the user select values — date range, product category, region. Whatever is selected filters every other visual on the page (unless the visual has been edited to not interact).

Sync slicers let the same slicer appear on multiple pages and stay in sync. Use View → Sync slicers to enable Sync and Visible per page.

Common pattern: a date slicer on every page, synced, so the user's chosen period follows them as they navigate.

Drill-down moves through a hierarchy within the same visual. Example: a bar chart by Year → drill to Quarter → drill to Month. The visual stays the same; the level changes.

Drill-through opens a separate report page filtered to the item you right-clicked. Example: right-click a customer name in a table and open a "Customer Detail" page filtered to that customer.

Drill-down stays in place; drill-through moves to a different page with the same filter applied.

A bookmark captures the current state of a report page — filter selections, slicer values, visible visuals and drill state. When the user clicks the bookmark, the report returns to that captured state.

Common uses:

• Toggle between two views of the same data — for example, chart view and table view.
• Reset all filters to a default state with a "Reset" button.
• Build a simple navigation menu with buttons that switch pages.

Bookmarks capture the state at the moment they are created. If you change the report layout, review bookmarks to ensure they still behave as intended.

Desktop is the workshop. Service is the showroom — where users actually consume the report.

A workspace is a container in the Power BI Service where a team stores and collaborates on datasets, reports and dashboards. Members have roles — Admin, Member, Contributor, Viewer.

An app is a packaged, read-only view of selected content from one or more workspaces. Users consume the app instead of the raw workspace, so they see only what you publish and cannot edit it.

Pattern: build and collaborate in the workspace; publish a curated app for the audience.

Context transition happens when CALCULATE is evaluated inside a row context. It converts the current row into an equivalent filter context.

That is why a measure works inside SUMX or FILTER even though it is defined as an aggregate:

High Value Customers =
COUNTROWS(
    FILTER(
        Customers,
        [Total Sales] > 100000
    )
)

Inside FILTER, the row context is the current customer. CALCULATE inside [Total Sales] converts that row into a filter, so the measure returns that customer's total.

FILTER returns a table of rows that match a boolean condition. It works on any table expression, not only on an existing table.

CALCULATETABLE evaluates a table expression in a modified filter context. It is to tables what CALCULATE is to scalar expressions.

// FILTER
Red Products =
FILTER( Product, Product[Color] = "Red" )

// CALCULATETABLE
Red Products 2 =
CALCULATETABLE(
    Product,
    Product[Color] = "Red"
)

In practice, prefer CALCULATETABLE when you can express the filter as a straightforward condition — it is usually faster. Use FILTER when the condition must be evaluated row by row using a measure.

• ALL(table) — returns the whole table ignoring any filter applied to it. Used inside CALCULATE to remove filters.
• ALLEXCEPT(table, column) — removes all filters on the table except the listed columns. Useful for "percent of category" while keeping the category filter.
• REMOVEFILTERS(table or column) — the modern, clearer name for the filter-removal behaviour. It does the same thing as ALL used as a filter, but the intent reads better.

% of All Products =
DIVIDE(
    [Total Sales],
    CALCULATE( [Total Sales], REMOVEFILTERS( Product ) )
)

New DAX code should use REMOVEFILTERS in place of ALL when the purpose is to remove filters — the intent is clearer to the next reader.

ALL removes filters from the column or table, so the calculation uses the full dataset — regardless of what the user selected.

ALLSELECTED removes filters but respects what the user has selected outside the current visual. In other words, it gives you the "percentage of the visible selection" rather than the "percentage of everything".

% of All =
DIVIDE( [Total Sales], CALCULATE( [Total Sales], ALL( Product ) ) )

% of Selected =
DIVIDE( [Total Sales], CALCULATE( [Total Sales], ALLSELECTED( Product ) ) )

Interviewers ask this because choosing the wrong one is a common bug: percentages that do not add to 100% inside a filtered page.

VAR / RETURN lets you name an intermediate result and reuse it in the same expression. Two benefits:

• Readability — the measure reads like a calculation, not a nested puzzle.
• Performance — DAX evaluates the variable once and reuses the value. A subexpression that appears in several places is computed only once.

YoY % =
VAR CurrentSales = [Total Sales]
VAR PriorSales   = [Sales LY]
RETURN
    DIVIDE( CurrentSales - PriorSales, PriorSales )

Variables are computed in the filter context where they are declared. They cannot be used outside the RETURN expression.

Incremental refresh loads only the new or changed rows on each refresh, instead of reloading the entire table.

You set two parameters — RangeStart and RangeEnd — and filter the fact table in Power Query using them. Power BI then splits the table into:

• Historical partitions — rows older than the refresh window, stored and not reloaded.
• Incremental partitions — rows inside the refresh window, reloaded on each refresh.

Benefits: faster refresh, less load on the source, and the ability to keep years of history in a dataset that only refreshes the recent slice.

The Performance Analyzer is a pane in Power BI Desktop that records how long each visual takes to render and breaks the time down into DAX, visual display and other components.

How to use it:

1. Open View → Performance Analyzer → Start recording.
2. Interact with the report — click a slicer, change a filter, refresh a visual.
3. Stop recording and expand the slowest visual.
4. Look at the DAX query time. If it dominates, the measure needs optimising. If the display time dominates, the visual is overloaded — too many rows, too many columns.
5. Copy the DAX query and test it in DAX Studio to dig deeper.

Always record on the slowest expected machine — a laptop with limited RAM — not on a fast development desktop.

Choose Import unless you have a specific reason for DirectQuery — real-time reporting, a dataset larger than the capacity allows, or a security requirement to not copy data.

• TOTALYTD, TOTALQTD, TOTALMTD — year, quarter and month-to-date totals.
• SAMEPERIODLASTYEAR — the same period in the previous year.
• DATEADD — shift the period by a number of days, months, quarters or years.
• PARALLELPERIOD — a full period offset from the current one.
• DATESINPERIOD — a rolling window of the last N days, months or years.
• PREVIOUSMONTH / PREVIOUSQUARTER / PREVIOUSYEAR — the immediately previous period.

All of these require a properly marked date table. Without it, the results are unpredictable.

Dynamic RLS uses the logged-in user's identity to filter the data, instead of hard-coding a value per role.

1. Create a mapping table that links user emails to the values they can see — for example, Email → Region.
2. Relate that table to the Region dimension.
3. In Manage roles, add a role with the filter: [Email] = USERPRINCIPALNAME().
4. Publish and assign users or groups to the role in the Service.

[Email] = USERPRINCIPALNAME()

Dynamic RLS scales to hundreds of users without creating hundreds of roles.

Reports are for analysis. Dashboards are for at-a- glance monitoring — a set of pinned tiles from different reports, refreshed together.

A calculated table is a table created with a DAX expression, instead of loaded from a source. It is stored in the model like a normal table.

Common uses:

• Building a Date table with CALENDAR or CALENDARAUTO.
• Creating a disconnected table for parameter selection, like a "what-if" analysis.
• Building a summarised aggregate table to speed up a slow measure.

Date =
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year",    YEAR([Date]),
    "Quarter", "Q" & QUARTER([Date]),
    "Month",   FORMAT([Date], "mmm")
)

Calculated tables use memory. For large fact tables, prefer doing the work in Power Query instead.

Rule: do data preparation in Power Query, do model logic in DAX. Only add a DAX calculated column if the value needs relationships or DAX-only functions.

SUM takes one column and adds its values in the current filter context. It does not evaluate any expression.

SUMX iterates a table, evaluates an expression for each row, then adds the results. It is used when the value you want to sum does not exist as a column.

// SUM — column exists
Total Qty =
SUM( Sales[Qty] )

// SUMX — expression must be computed
Total Sales =
SUMX( Sales, Sales[Qty] * Sales[Price] )

Interviewers ask this because many candidates write SUMX when SUM would be faster and clearer. Use the simplest function that expresses the calculation.

A many-to-many relationship exists when neither side is unique — for example, a Student table and a Course table, where students take many courses and courses have many students.

Two ways to handle it in Power BI:

1. Bridge table — create an Enrollment table with StudentID and CourseID. This bridge connects Student and Course, turning the many-to-many into two one-to-many relationships. This is the preferred modelling pattern.
2. Native many-to-many relationship — Power BI allows a direct many-to-many relationship between two tables. It works, but filter behaviour can be unpredictable and DAX often returns unexpected results.

Always prefer the bridge table. It makes the model easier to reason about and the measures predictable.

Step 1 — Understand the questions. List the business questions the report must answer. Model design follows from those questions, not the other way around.

Step 2 — Identify fact and dimension tables. Facts contain measurable events — Sales, Orders, Payments. Dimensions describe them — Product, Customer, Date, Store, Employee.

Step 3 — Build a star schema. One fact table in the centre, dimensions around it, all relationships one-to-many and single-direction.

Step 4 — Create a date table. One row per date, marked as a date table, connected to every fact table that needs time intelligence.

Step 5 — Clean in Power Query. Rename, change types, remove duplicates, unpivot wide columns. Keep the model simple.

Step 6 — Write measures, not columns. Use DAX measures for aggregation. Use calculated columns only when the value must be sliced or filtered.

Step 7 — Measure, then optimise. Run the Performance Analyzer on the slowest visuals and reduce cardinality, remove unused columns, and replace row-by-row DAX with set-based patterns.

Order of impact, from biggest to smallest:

1. Reduce model size. Remove unused columns, remove unused tables, and change high-cardinality text columns to integer keys where possible.
2. Fix the relationships. Ensure they are one-to-many and single-direction. Avoid bidirectional filters unless there is a strong reason.
3. Simplify measures. Replace row-by-row FILTER over a fact table with set-based CALCULATE and KEEPFILTERS where possible. Use variables to avoid recomputation.
4. Reduce visual count. A page with 25 visuals executes 25 queries. Split content across pages or use drill-through.
5. Use the Performance Analyzer. Identify the slowest visual, then check whether DAX or rendering is the bottleneck.
6. Switch to Import. If the model uses DirectQuery and freshness allows, Import is almost always faster.

Optimisation is a measurement exercise, not a guessing exercise. Fix the biggest bottleneck first, then re-measure.

Gateway: if the data source is inside the company network, install an on-premises data gateway on a machine that can reach it. Register the gateway in the Power BI Service, then add the data source and credentials.

Scheduled refresh: in the Service, open the dataset settings → Scheduled refresh. Set the frequency (up to 8 times a day on Pro; more on Premium), the time zone, and the notification options for failures.

Things to check when refresh fails:

• Credentials have not expired or been rotated.
• The gateway machine is online and the gateway service is running.
• The dataset is not too large for the capacity's refresh window.
• The source schema has not changed — a renamed column breaks the query.

Workspace roles control who can work inside the workspace: Admin, Member, Contributor, Viewer. These are for the build team.

App audiences control who sees what when the app is published. You can create audiences — for example, Sales, Finance, Leadership — and give each audience a different set of reports and dashboards.

Pattern: editors get workspace access; consumers get app access. Never give a consumer workspace access just so they can view a report — publish an app instead.

Several techniques, chosen based on the size and freshness requirement:

• Incremental refresh — load historical data once, then refresh only the recent window.
• Aggregations — keep a small aggregate table (monthly totals) for high-level visuals, and a detail table for drill-through.
• Reduce columns — every column costs memory. Keep only what the report needs.
• Reduce cardinality — remove decimals from ID columns, split date and time, and replace long strings with keys.
• Composite model — keep dimensions and small facts in Import, leave the very large fact in DirectQuery.
• Premium capacity — larger datasets and larger refresh windows.

The right mix depends on whether the priority is report speed, freshness or storage cost. State the trade-off in the interview.

In Indian enterprises, Power BI is very common because of Microsoft 365 bundling. Tableau is strong in analytics-heavy teams and some product companies. Knowing both, and the trade-offs, is a strong interview answer.

Do not create a role per user. Use dynamic RLS with a user mapping table:

1. Create a table UserRegion with columns Email, Region.
2. Relate it to the Region dimension in the model.
3. Create a single role with the filter [Email] = USERPRINCIPALNAME().
4. Assign the role to a security group in the Service, and manage membership in Microsoft Entra (Azure AD).

When a new user joins, add them to the mapping table and the security group. No new roles, no report changes. This scales to thousands of users with one role definition.

A deployment pipeline manages the lifecycle of Power BI content across stages — typically Development → Test → Production.

How it works:

• Each stage is a workspace with the same content.
• You deploy from Development to Test after the change is reviewed.
• You deploy from Test to Production after testing.
• Parameters and data source rules change the connection details per stage — so Test points at Test data, Production at Production data.

Deployment pipelines need Premium or Fabric capacity. They replace the manual "download pbix, upload to another workspace" pattern that causes most deployment mistakes.

The Power BI admin portal has an Activity log and an Audit log that record views, shares, refreshes and exports.

Where to look:

• Usage metrics — per-report view counts and viewer names, from the report itself (Requires Pro or above).
• Admin portal → Audit logs — exports to Microsoft Purview for long-term retention.
• Lineage view — shows which reports depend on which datasets, and which datasets feed which reports.

For governance, review usage metrics monthly, identify unused reports, and archive them so users see a clean library.

Pattern: build a dataflow once to clean and standardise the shared data. Then multiple datasets reuse the same dataflow. It removes duplicated Power Query work across teams.

A calculation group is a table that applies the same calculation to any measure in a visual. The classic example is a "Time Intelligence" calculation group with items like Current, YTD, LY and YoY %.

Instead of writing twelve measures for each base measure, you write one calculation group and reuse it. The user drags the calculation group into the columns area and switches between Current, YTD, LY and YoY.

Calculation groups need Tabular Editor to create, and they work best in enterprise models with many base measures. For a small model, they may be overkill.

In a Fabric architecture, the lakehouse stores the raw and curated data, and Power BI consumes it through a semantic model built on top.

Typical pattern:

1. Data lands in OneLake as files or tables.
2. Dataflows Gen2 or notebooks clean and shape it.
3. A warehouse or lakehouse stores the curated star schema.
4. Power BI builds a semantic model on the curated tables, adds measures, and serves reports.
5. Direct Lake mode queries the lakehouse without importing the data — combining DirectQuery freshness with Import-like speed.

In interview terms, knowing where Power BI sits in the wider data platform is often more important than knowing a specific tool version.

Step 1 — Check the visual-level filter. A filter added to the visual overrides the slicer.

Step 2 — Check the measure. If the measure wraps the sum in SAMEPERIODLASTYEAR or DATEADD without a variable, it may be returning a shifted period.

Step 3 — Check the relationship between the fact table and the date table. If the relationship is inactive, the measure is not filtered by the date slicer at all.

Step 4 — Check whether the date table is marked as a date table. Without marking, time intelligence functions fall back to the fact table's date column, which may only cover a partial period.

Step 5 — Add a card visual of the selected date range to confirm what the slicer is actually passing to the measure.

Step 1 — Open Performance Analyzer and identify the slowest visual. If DAX dominates, the measure needs fixing. If rendering dominates, the visual is too heavy.

Step 2 — Reduce the number of visuals per page. Ten visuals = ten queries. Split the page or use bookmarks.

Step 3 — Check the measures on the slow visual. Replace row-by-row FILTER over a fact table with a CALCULATE filter on a dimension.

Step 4 — Check for high-cardinality columns in the model. Text columns with thousands of unique values cost memory and slow aggregation.

Step 5 — Check the relationship direction. Bidirectional filters on a large fact table can be very expensive; change to single direction if possible.

Step 6 — If the model uses DirectQuery, switch to Import if the freshness requirement allows. Import is almost always faster.

Step 1 — Confirm the date table exists, is marked, and has a relationship to the fact table.

Step 2 — Build a base measure. In the sample model on this page, that is Total Sales = SUMX(Sales, Sales[Qty] * Sales[Price]).

Step 3 — Build the prior-year measure with SAMEPERIODLASTYEAR:

Sales LY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR( 'Date'[Date] )
)

Step 4 — Build the YoY % measure with DIVIDE, so it returns blank instead of dividing by zero:

YoY % =
DIVIDE(
    [Total Sales] - [Sales LY],
    [Sales LY]
)

Step 5 — Build YTD with TOTALYTD:

Sales YTD =
TOTALYTD(
    [Total Sales],
    'Date'[Date]
)

Step 6 — Format the YoY % measure as a percentage in the model, and place the three measures in a matrix by Year.

Step 1 — Confirm the model has a Region dimension related to the fact table.

Step 2 — Create a user mapping table — Email → Region — and relate it to the Region dimension.

Step 3 — Create one role in Manage roles with the filter [Email] = USERPRINCIPALNAME() on the mapping table.

Step 4 — Test in Desktop with View as role, entering different emails to verify the filter changes.

Step 5 — Publish the dataset. In the Service, assign the role to a security group that contains all regional users.

Step 6 — Test in the Service with "Test as role" to confirm the filter works after deployment.

This is the standard dynamic RLS pattern. It scales without creating a role per region.

Step 1 — Remove the 44 columns you do not need in Power Query, before the data reaches the model. This reduces model size and refresh time.

Step 2 — Do not remove the columns at the source — another report may need them. Remove them in this dataset only.

Step 3 — Check data types. Convert text columns that are actually numbers into numeric types, and remove the time part from datetime columns if it is not used.

Step 4 — If the remaining six columns include high-cardinality text, consider replacing the text with an integer ID and putting the text in a dimension table.

Step 5 — After refreshing, compare the model size before and after. Reducing columns usually reduces size by far more than the column count suggests, because compression improves.

Step 1 — Confirm the measure is not a sum of an average or a ratio. Summing percentages or ratios gives a mathematically wrong total.

Step 2 — Check for context transition issues. If the measure uses an iterator like SUMX or AVERAGEX over a table that has a filter applied, the total is calculated over the filtered set, not the raw set.

Step 3 — Check that the measure is not a calculated column. A calculated column is summed row by row; a measure is aggregated at the visual's filter context.

Step 4 — Test the measure at the grand total level by removing all filters. If the total matches the raw sum, the issue is only the ratio or average type.

Step 5 — For ratio measures, use SUMX over the base quantities instead of a sum of ratios:

Total Margin % =
DIVIDE(
    SUMX( Sales, Sales[Margin] ),
    SUMX( Sales, Sales[Revenue] )
)

Step 1 — Open the refresh history in the Service and read the error message. It usually names the source that failed.

Step 2 — Check whether the credentials for that source have expired or been rotated. Many enterprise sources force a password change every 90 days.

Step 3 — Check the gateway. If the gateway machine was rebooted or the gateway service restarted, the connection may have failed once and recovered.

Step 4 — Check the service account used by the gateway. If its password expired, all refreshes fail until it is updated.

Step 5 — Enable refresh failure notifications for the dataset so the team is alerted the moment it happens, not after a user reports stale data.

Step 1 — Build a base measure Total Sales.

Step 2 — Build a rank measure with RANKX over the Product table:

Product Rank =
RANKX(
    ALL( Product[ProductName] ),
    [Total Sales],
    ,
    DESC,
    DENSE
)

Step 3 — Build a measure that returns the product name if its rank is ten or less, and "Others" otherwise:

Top 10 Group =
VAR CurrentRank = [Product Rank]
RETURN
    IF(
        CurrentRank <= 10,
        SELECTEDVALUE( Product[ProductName] ),
        "Others"
    )

Step 4 — Use the Top 10 Group measure as the row field in a bar chart, and Total Sales as the value.

Step 5 — Sort by Total Sales descending. The chart now shows ten products plus one "Others" row for everything below rank ten.

Step 1 — Ask for the Excel file and the exact cells the user compared. Do not assume the discrepancy is real.

Step 2 — Check the definition of the measure. Excel may use a different definition of "sales" — gross vs net, before or after returns, inclusive or exclusive of tax.

Step 3 — Check the date range. Excel may include the last day of the month while Power BI's year-to-date has not refreshed yet.

Step 4 — Check the data source. Excel may be reading the source directly while Power BI reads a data warehouse or a curated view.

Step 5 — Document the measure definition and the source in the report — a "Definitions" page or tooltip. Most mismatches are definition mismatches, not data mismatches.

Step 1 — Build a measure that reads the selected values:

Chart Title =
VAR SelectedYear =
    SELECTEDVALUE( 'Date'[Year], "All Years" )
VAR SelectedCategory =
    SELECTEDVALUE( Product[Category], "All Categories" )
RETURN
    "Sales for " & SelectedCategory & " in " & SelectedYear

Step 2 — Select the chart, open the Format pane, and find Title.

Step 3 — Turn on the fx (conditional formatting) button next to Title Text.

Step 4 — Choose "Field value" and select the Chart Title measure.

Step 5 — Test by changing the slicer. The title updates to match the selection — "Sales for Electronics in 2025", and so on.

Total Sales =
SUMX(
    Sales,
    Sales[Qty] * Sales[Price] * ( 1 - Sales[Discount] )
)

Why SUMX: the line total does not exist as a column. SUMX iterates Sales and computes the expression row by row.

Expected result: a currency value per filter context. In a matrix by Category, each cell shows that category's sales; the grand total is the model total.

Sales LY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR( 'Date'[Date] )
)

Why CALCULATE: you need to shift the date filter for the base measure. CALCULATE modifies the filter context so the same period one year earlier is used.

Requires: a marked date table with a relationship to Sales.

Expected result: the same period one year earlier. In a matrix by Year, the 2025 column shows the value that the 2024 column shows in the Total Sales measure.

YoY % =
DIVIDE(
    [Total Sales] - [Sales LY],
    [Sales LY]
)

Why DIVIDE: it returns BLANK instead of an error when the denominator is zero. Never use the / operator in a ratio measure.

Expected result: a decimal that formats as a percentage. Positive means growth over last year, negative means a drop, blank means there is no prior-year data.

Sales YTD =
TOTALYTD(
    [Total Sales],
    'Date'[Date]
)

What it does: returns the cumulative total from the start of the year to the last date in the current filter context.

Expected result: by month, the value grows through the year and resets in January. In December it equals the full year's sales.

Running Total =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL( 'Date'[Date] ),
        'Date'[Date] <= MAX( 'Date'[Date] )
    )
)

How it works: remove all date filters with ALL, then re-apply a filter that keeps every date up to and including the current date. The base measure sums across that window.

Expected result: a cumulative line that grows through the year. It resets at the start of each year if you put Year in the visual's rows.

% of All =
DIVIDE(
    [Total Sales],
    CALCULATE( [Total Sales], REMOVEFILTERS( Product ) )
)

% of Selected =
DIVIDE(
    [Total Sales],
    CALCULATE( [Total Sales], ALLSELECTED( Product ) )
)

When to use which: use % of All when the denominator should always be the model total. Use % of Selected when the denominator should be the total of what the user has filtered — so percentages add to 100% within the visible selection.

Expected result: a decimal that formats as a percentage. For % of Selected, the values sum to 100% in the visible rows.

Product Rank =
RANKX(
    ALL( Product[ProductName] ),
    [Total Sales],
    ,
    DESC,
    DENSE
)

Why ALL: RANKX must iterate every product, not only the products in the current filter. ALL removes the filter so all product names are considered.

Why DENSE: DENSE gives ties the same rank and no gaps — two products tied at rank 3 means the next is rank 4, not rank 5.

Expected result: a whole number, 1 for the highest- selling product in the filter context.

Distinct Customers =
DISTINCTCOUNT( Sales[CustomerKey] )

Why DISTINCTCOUNT: a customer may place many orders. SUM or COUNT would count rows; DISTINCTCOUNT counts unique customers.

Expected result: a whole number. In a region-level visual it is the number of unique customers per region.

Average Order Value =
DIVIDE(
    [Total Sales],
    DISTINCTCOUNT( Sales[SaleID] )
)

Why DIVIDE: safe division — if there are no orders in the filter, the measure returns BLANK instead of an error.

Expected result: a currency value. Across all sales, it is total sales divided by the number of distinct orders. In a category-level visual, it is that category's average order value.

Sales Red =
CALCULATE(
    [Total Sales],
    Product[Color] = "Red"
)

What CALCULATE does: takes the current filter context and adds a filter on Product[Color].

Expected result: a currency value for Red products only. It is unaffected by a page colour slicer — the measure always shows Red, no matter what the user selects.

Top 5 Products =
VAR Top5 =
    TOPN(
        5,
        ALL( Product[ProductName] ),
        [Total Sales],
        DESC
    )
RETURN
    CALCULATE( [Total Sales], Top5 )

How it works: TOPN returns a table of the top five product names by Total Sales. CALCULATE then restricts the base measure to those products.

Expected result: a currency value that equals the combined sales of the top five products. In a card visual it shows a single number; in a table visual it shows the contribution per product.

Dynamic Title =
VAR SelectedYear =
    SELECTEDVALUE( 'Date'[Year], "All Years" )
VAR SelectedCategory =
    SELECTEDVALUE( Product[Category], "All Categories" )
RETURN
    "Sales for " & SelectedCategory & " in " & SelectedYear

How to wire it up: in the Format pane for the visual title, turn on fx and select "Field value" → Dynamic Title measure.

Expected result: the title changes with the slicers — "Sales for Electronics in 2025". If no filter is selected, the fallback text appears — "Sales for All Categories in All Years".