Skip to content
Tech Interview Prep
Technical interview guide

Calculated Measures & Aggregations

Writing correct aggregations and calculated measures, where subtle mistakes silently produce wrong numbers.

Read
45 min
Practice MCQs
25
Interview Q&A
25
Edition
v4
Editorial status
Reviewed

Scope: Current DAX and Microsoft Power BI, Tableau calculations and LOD expressions, and LookML measure guidance reviewed 2026-09-04.

Curated: · Written: · Reviewed:

25 questions

MCQ-5A weekly acquisition report lists paid search at 20% conversion (40 of 200 sessions) and email at 10% (80 of 800 sessions). The summary row reports 30% as the blended conversion rate. What is the correct blended conversion rate for the two channels combined?
MCQ-16A payment-splitting service divides a $10.00 restaurant bill equally among 3 people. It stores each person's share as an integer number of cents and must make the shares sum exactly to $10.00. The current code computes 1000 / 3 = 333.333... and rounds each share half-up to 333 cents, so the three shares sum to $9.99. What should the service do instead?
MCQ-6A month-end report must state each customer's card balance as of 31 March and the total across customers. The ledger stores one row per account per day, daily_balance(account, day, closing_balance_usd), and for 29-31 March it holds, in day order: account A = 100, 120, 130; account B = 80, 60, 70 (USD). Which total belongs on the report?
MCQ-4A Sales table has one row per order line, with Quantity and UnitPrice columns and no line-amount column. The measure Total Revenue = SUM(Sales[Quantity]) * SUM(Sales[UnitPrice]) overstates revenue whenever lines carry different unit prices, and the model cannot be changed: no new calculated columns. Which definition returns the correct total?
MCQ-8A Sales table holds five rows: (OrderID, Customer, Amount): (1, A, 10), (2, A, 20), (3, B, 30), (4, C, 40), (5, C, 50). A developer adds a calculated column with RowTotal = CALCULATE(SUM(Sales[Amount])). What values does RowTotal hold?
MCQ-3A DAX model has Product (Category) related one-to-many to Sales (Amount). Sales holds Bikes 1000 and 2000, Clothing 300 and 100, Accessories 50. A matrix visual puts Product[Category] on rows, shows a grand total row, and displays one measure: Clothing Sales = CALCULATE([Total Sales], Product[Category] = "Clothing") where [Total Sales] = SUM(Sales[Amount]). What does the matrix display?
MCQ-20A Sales table has four rows: (Region = EU, Product = A, Amount = 300), (Region = US, Product = A, Amount = 700), (Region = EU, Product = B, Amount = 200), (Region = US, Product = B, Amount = 800). A measure is defined as: ``` EU A Sales = CALCULATE ( VAR TotalSales = SUM ( Sales[Amount] ) RETURN TotalSales, Sales[Region] = "EU", Sales[Product] = "A" ) ``` The measure is placed on a card visual with no slicers and no page filters. What number does the card show?
MCQ-2A Power BI model has a Sales table with Amount, ProductKey, and RegionKey. Product[Category] has values Bikes, Clothing, Accessories. Region[Name] has values West, East. The data is: West: Bikes 100, Clothing 50, Accessories 25 East: Bikes 200, Clothing 150, Accessories 75 A report page has a slicer on Region[Name] set to West. A matrix has rows from Product[Category]. The measure is: Current Category West = CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Product, Product[Category])) What value appears in the Bikes row of the matrix?
MCQ-9A model has a Product dimension with Color and a Sales fact with Amount; the totals are Red 400, Blue 100, Green 250. A report page has a slicer on Product[Color] with only Red selected and a card visual bound to the measure Flagged Sales = CALCULATE ( SUM ( Sales[Amount] ), KEEPFILTERS ( Product[Color] = "Blue" ) ). What does the card display?
MCQ-17A Power BI model has a measure `Margin = DIVIDE( SUM(Sales[Profit]), SUM(Sales[Revenue]) )`. In a matrix by Region and Month, the North region for January shows a blank cell. A developer adds two test measures: `Margin + 1` returns 1, and `Margin * 2` returns blank. The North region had no sales rows in January. What does the blank in `Margin` mean here?
MCQ-7A model has a Sales table with columns Store, Profit and Revenue. Three stores: A (Profit 300, Revenue 1000), B (Profit 0, Revenue 0), C (Profit 200, Revenue 500). Two measures are defined: Margin % = DIVIDE( SUM(Sales[Profit]), SUM(Sales[Revenue]) ) Avg Margin = AVERAGEX( VALUES( Sales[Store] ), [Margin %] ) The team rewrites Margin % as DIVIDE( SUM(Sales[Profit]), SUM(Sales[Revenue]), 0 ) so the matrix shows 0% instead of a blank for Store B. After the change, what does Avg Margin return?
MCQ-14A Power BI matrix groups sales by Category and shows a DAX measure defined as Distinct Customers = DISTINCTCOUNT(Sales[CustomerKey]). The underlying Sales rows are: Category A: C1, C1, C2, C3; Category B: C3, C4, C5, C6; Category C: C6, C6, C7. The three Category rows display 3, 4, and 2. What value does the grand total display, and why?
MCQ-15A retail chain's regional warehouses each hold a customers table, and each regional dashboard reports COUNT(DISTINCT customer_id) for the month: NA 1,200, EMEA 900, APAC 700. Customers can shop in more than one region. A one-off overlap audit for the same month found 300 customers in both NA and EMEA, 250 in both NA and APAC, 200 in both EMEA and APAC, and 100 in all three regions. What is the correct global distinct-customer count for the month?
MCQ-13A dashboard tile reads "Q3 Revenue YoY: −54%". It compares Q3 2024 to date (Jul 1 – Aug 15, 46 days, $9.2M) against the full Q3 2023 (Jul 1 – Sep 30, 92 days, $20.0M). The prior-year revenue for the same 46-day window (Jul 1 – Aug 15, 2023) was $11.5M, and for the last 46 days of Q3 2023 (Aug 16 – Sep 30) it was $8.5M. What YoY change should the tile report?
MCQ-12A Power BI model has a Date table with a Date column and a Sales table with OrderDate, ShipDate and Amount. Sales[OrderDate] → Date[Date] is the active relationship; Sales[ShipDate] → Date[Date] exists but is inactive. Sales holds three rows: (OrderDate 2024-01-05, ShipDate 2024-02-10, Amount 100), (OrderDate 2024-02-20, ShipDate 2024-02-25, Amount 200), (OrderDate 2024-02-01, ShipDate 2024-03-15, Amount 400). A measure is defined as Shipped Revenue = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Date[Date])). In a matrix with Date[Month] on rows, what does the February 2024 row show?
MCQ-11A Power BI model has DimProduct (ProductKey 1 = Widget, 2 = Gadget), FactSales (ProductKey 1 → 100, 2 → 200) and FactReturns (ProductKey 1 → 10, 2 → 20). FactSales[ProductKey] → DimProduct[ProductKey] is single-direction; FactReturns[ProductKey] → DimProduct[ProductKey] is set to Both. A report page carries a page-level filter FactReturns[Amount] > 15 and a card showing Total Sales = SUM(FactSales[Amount]). What does the card display?
MCQ-1A Power BI model holds a 12-million-row Sales fact table and sits close to the 1 GB dataset limit. A report needs a Margin % that is the ratio of totals over whatever date range, region slicer, and matrix grouping the user picks. Which approach fits?
MCQ-24A margin measure divides [Profit] by [Revenue], where [Revenue] is a measure that scans a large fact table. A developer replaces `IF([Revenue] <> 0, [Profit] / [Revenue], BLANK())` with `DIVIDE([Profit], [Revenue])` and the query gets faster. Which mechanism best explains the improvement?
MCQ-19Sales has 180,000,000 rows. A report page's slicer selects one month, leaving 3,000,000 rows in the filter context; 700,000 of those have Sales[Qty] > 0. The measure is: ``` Revenue := CALCULATE ( SUMX ( Sales, Sales[Qty] * Sales[Price] ), Sales[Qty] > 0 ) ``` What does the storage engine return to the formula engine for this visual?
MCQ-23In a composite model combining an in-memory Import aggregation table with a massive DirectQuery fact table, how does the VertiPaq engine optimize measure query execution?
MCQ-22When evaluating query execution plans in DAX, what is the primary architectural and performance difference between using SUMMARIZE and SUMMARIZECOLUMNS to group and aggregate measure results?
MCQ-21A developer writes SUMX(Sales, [Total Profit]), where [Total Profit] is a measure rather than a column. Which statement describes how DAX handles the row context at each iteration?
MCQ-25A matrix visual puts Product on rows, and a Product slicer lets the user narrow the report to a subset of products. The measure must show each product's share of the total for the products the user selected, so the denominator has to ignore the row's own product but keep the slicer's selection. Which statement about the filter-removal function the denominator needs is correct?
MCQ-10A semantic model has a Sales table with columns Region, Product, Year, and Amount, plus a row-level security rule that restricts each viewer to their own region. A report shows Product on rows and a 'Share of total' measure whose denominator is SUM(Amount) over the current filter context. The page filter is Year = 2024, and the viewer is in EMEA. The table holds: EMEA/Widgets/2024 = 300, EMEA/Gadgets/2024 = 200, APAC/Widgets/2024 = 600, APAC/Gadgets/2024 = 450, EMEA/Widgets/2023 = 100, EMEA/Gadgets/2023 = 150, APAC/Widgets/2023 = 200, APAC/Gadgets/2023 = 250. The analyst wants the share to mean 'this product's 2024 revenue as a fraction of the viewer's total 2024 revenue across all products'. What value should the EMEA/Widgets cell show?
MCQ-18Contoso's Power BI semantic model holds Sales (Region, Amount) with Germany 1.1M, France 1.5M and Spain 1.6M, and Managers (Email, Region) with one row per email. Every report visual uses a single measure, defined in DAX: Secure Amount = IF ( SELECTEDVALUE ( Sales[Region] ) = LOOKUPVALUE ( Managers[Region], Managers[Email], USERPRINCIPALNAME () ), SUM ( Sales[Amount] ), BLANK () ) Anna, anna@contoso.com with the Managers row (anna@contoso.com, Germany), has read and build access to the dataset. In the sales matrix she sees Germany 1.1M and no France or Spain rows. The model defines no RLS roles and no other filters. She then uses Analyze in Excel on the same model, puts Region on Rows and the Amount column (not Secure Amount) in Values, and applies no filters. What does her PivotTable show?