Data analyst interview questions

One project, step by step: the South region's sales head asks, "Why are fewer customers coming back to order again?"

What interviewers listen for: that you start from the business decision, not the data; that you check the data before trusting it; and that you finish with a recommendation, not a pile of charts.

How to structure your answer: walk through the steps using a real project of your own if you have one, even a college project, naming one specific problem you hit at each step.

Follow-ups

• Which step takes the longest? Usually cleaning and preparing the data, often more than half the time.
• What if the data can't answer the question? Say so early, explain what's missing, and suggest the closest question it can answer or how to collect what's needed.

The pattern: moving down the table, the questions get harder to answer and the answers become more valuable, because they lead more directly to decisions.

Follow-ups

• Which type is a monthly sales dashboard? Descriptive, with some diagnostic power if it lets users drill down by region and category.
• Is an A/B test diagnostic or prescriptive? It tests whether a change causes an improvement, so it supports a prescriptive decision: ship the change or don't (question 36).

The raw data

Step 1: clean the text, then map the variants. A small mapping table in E2:F3 lists each known variant and its standard name (Bangalore → Bengaluru, Bombay → Mumbai). In D2, filled down:

=XLOOKUP(PROPER(TRIM(B2)), $E$2:$E$3, $F$2:$F$3, PROPER(TRIM(B2)))

TRIM removes the stray spaces and PROPER fixes the capitals, so all three spellings become either "Bangalore" or "Bengaluru"; the lookup then maps "Bangalore" to "Bengaluru". Cities not in the table, like Mysuru, pass through unchanged. In Power Query, the same steps are Transform → Format → Trim, Capitalize Each Word, then Replace Values.

Step 2: decide what to do with each blank

Why filling with 0 is dangerous: AVERAGE skips blank cells but counts zeros. The five known spends above average ₹3,520; fill C4's blank with 0, and the average drops to ₹2,933, a customer who never existed at ₹0 pulling it down.

Follow-ups

• How do you find all the variants of a city? Build a pivot table, or use UNIQUE, on the city column after TRIM and PROPER, and read the list; variants like "Bengaluru", "Bangaluru" and "Blr" stand out.
• Should you delete rows with blanks? Only from the analysis that can't use them, and only after checking they're few and random. Deleting them from the source data loses information other analyses might need.

Ten South-region orders from one day, in A2:A11: 640, 899, 1,199, 1,350, 1,499, 1,799, 2,100, 2,499, 2,999 and 48,000.

Anything above ₹4,143 is an outlier, so the ₹48,000 order is flagged.

Why the IQR rule beats the z-score here: the z-score of the ₹48,000 order is

=(A11-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11)

which gives 2.84, below the usual cut-off of 3. The outlier inflates the standard deviation it's measured against, so it hides itself. Quartiles barely move when one extreme value is added, which makes the IQR rule more robust on small or skewed data.

What one outlier does to a summary

Keep or remove?

Follow-ups

• What does a box plot show? The median, the quartiles as a box, whiskers out to the furthest points within 1.5 × IQR, and outliers as separate dots.
• Would you ever cap outliers? Sometimes, for modelling, by replacing values beyond a percentile with that percentile (winsorising). For reporting, it's better to show them honestly.

Usual causes of a mismatch

Reconcile at order level. Export order IDs and amounts from both reports into one workbook. In your report's sheet, flag orders Finance doesn't have:

=IF(COUNTIF(Finance!A:A, A2)=0, "Missing in Finance", "")

and the same in reverse on Finance's sheet. Then group the unmatched orders by cause.

The bridge, which explains the whole difference:

Which number is right? Both can be, for different purposes. Finance's figure is right for the accounts; yours may be right for measuring customer demand. The fix is to label each report with its definition, or agree one shared definition.

Follow-ups

• What if you can't explain the whole gap? Report the part you've explained, the unexplained remainder, and what you're checking next. An honest "₹0.3 lakh still unexplained" beats a forced match.
• How do you stop it happening again? Write the metric definition down, ideally in a shared data dictionary, and add an automatic check that compares the two totals every month.

The same lookup, three ways: the Category for the Product ID in Sales!E2 (P-012, so "Home & Kitchen").

=VLOOKUP(E2, Products!$A:$D, 3, FALSE)
=INDEX(Products!$C:$C, MATCH(E2, Products!$A:$A, 0))
=XLOOKUP(E2, Products!$A:$A, Products!$C:$C)

Looking left: to find a product's ID from its name, the lookup column (B, Product Name) is to the right of the result (A, Product ID). VLOOKUP can't do that; the others can:

=XLOOKUP("Smart Watch", Products!$B:$B, Products!$A:$A)

That returns P-007.

The approximate-match trap: leave out VLOOKUP's last argument and it does an approximate match, which returns the nearest value below when there's no exact one. On the sorted Products sheet,

=VLOOKUP("P-030", Products!$A:$D, 3)

returns "Stationery", the category of P-023, instead of an error, so a missing product silently gets the wrong category. Always pass FALSE (or 0) for exact matches.

Which to pick: XLOOKUP where everyone has Microsoft 365; INDEX-MATCH when the file is shared with people on older Excel; VLOOKUP only for quick lookups, with FALSE.

Follow-ups

• Why is INDEX-MATCH said to be faster? On big sheets, it reads only the two columns it needs, while VLOOKUP's range covers every column in between; with today's Excel the difference is small.
• What does IFNA do better than IFERROR? It catches only #N/A, the "not found" error, so real mistakes such as a #REF! from a deleted column still show up.

What happens when a formula in C2 is copied to D3, one column right and one row down:

Each one on the shared dataset

• Relative: Sales Amount in H2 is =F2*G2; filled down, row 16 correctly becomes =F16*G16.
• Absolute: each order's share of all sales, in L2: =H2/SUM($H$2:$H$16). Without the $ signs, the total's range would slide down as the formula is filled, and every share after the first would be wrong.
• Absolute, for an input cell: with the GST rate of 18% in P1, the tax on each order is =H2*$P$1.
• Mixed: a price grid with prices down column A and discount rates across row 1. In B2, filled across and down:

=$A2*(1-B$1)

Every cell then takes the price from its own row (column A locked) and the rate from its own column (row 1 locked).

Follow-ups

• Where else do mixed references matter? In conditional formatting, which applies one formula to every selected cell as if it had been copied there (question 13).
• What about Excel Tables? Structured references such as Sales[@Quantity] don't shift at all, which avoids most of these mistakes.

A discount column, A2:A8, imported from a CSV

=COUNT(A2:A8)
=COUNTA(A2:A8)
=COUNTBLANK(A2:A8)
=COUNTIF(A2:A8, "NA")

These return 3, 6, 2 and 1.

The two surprises

• A6 is counted by both COUNTA and COUNTBLANK. A formula returning "" isn't empty to COUNTA, but COUNTBLANK treats empty text as blank. That's Microsoft's documented behaviour, and it's why the three counts don't add up to 7.
• A8's "120" is invisible to COUNT and to SUM. It's text, so SUM(A2:A8) is 325, not 445. Convert it with VALUE, Text to Columns, or Convert to Number from the green warning triangle.

Follow-ups

• How do you count cells meeting two conditions? COUNTIFS, such as =COUNTIFS(Sales!D:D, "South", Sales!H:H, ">=10000"), which counts South orders worth ₹10,000 or more (1 in the sample).
• How do you count unique values? In Microsoft 365, =COUNTA(UNIQUE(Sales!C2:C16)) gives 8 customers; in a pivot table, use Distinct Count (question 37).

Step 1: H1 is the header "Sales Amount"; in H2, filled down to H16:

=F2*G2

Step 2: in any cell outside columns B, D and H:

=SUMIFS(H:H, D:D, "South", B:B, ">="&DATE(2025,3,1), B:B, "<"&DATE(2025,4,1))

Which orders it adds

A reusable version: put the region in M1 ("South") and the first day of the month in M2 (01-Mar-2025):

=SUMIFS(H:H, D:D, $M$1, B:B, ">="&$M$2, B:B, "<"&EDATE($M$2,1))

EDATE($M$2,1) is the same day one month later, 1 April, so changing M2 to any month gives that month's total.

Common mistakes

• Typing the date as text, ">=01/03/2025": whether that's 1 March or 3 January depends on the computer's regional settings. DATE(2025,3,1) is unambiguous.
• Mixing up the argument order: SUMIFS takes the sum range first; the older SUMIF takes it last.
• Filtering on the month alone, such as MONTH(B2)=3, which also catches March 2024.

Follow-ups

• How would you do this without a formula? A pivot table with Region as a filter and dates grouped by year and month (question 12).
• How do you count the orders instead? The same conditions in COUNTIFS: =COUNTIFS(D:D, "South", B:B, ">="&DATE(2025,3,1), B:B, "<"&DATE(2025,4,1)) returns 4.

I1 "Category" and J1 "Unit Cost"; in I2 and J2, filled down:

=XLOOKUP(E2, Products!$A:$A, Products!$C:$C, "Not found")
=XLOOKUP(E2, Products!$A:$A, Products!$D:$D, "Not found")

For Excel 2019 and older:

=IFNA(INDEX(Products!$C:$C, MATCH(E2, Products!$A:$A, 0)), "Not found")
=IFNA(INDEX(Products!$D:$D, MATCH(E2, Products!$A:$A, 0)), "Not found")

The results

Three details interviewers like

• The $ signs keep the lookup ranges fixed as the formula is filled down (question 7).
• "Not found" in a number column has a cost: any calculation using J13, such as profit, returns #VALUE!. That's arguably useful, since it forces someone to fix the Products table, but for totals you'd filter the row out or return a blank instead.
• IDs that look identical but don't match usually have a stray space or are numbers stored as text. XLOOKUP(TRIM(E2), …) fixes the first; consistent data types fix the second.

Follow-ups

• IFERROR or IFNA? IFNA catches only "not found" (#N/A); IFERROR also hides genuine mistakes, such as a typo in the sheet name.
• How would you list the missing products? Filter column I for "Not found", or in Microsoft 365, =UNIQUE(FILTER(E2:E16, I2:I16="Not found")).

K1 "Size"; in K2, filled down:

=IFS(H2>=10000, "High", H2>=2000, "Medium", TRUE, "Low")

The final TRUE, "Low" is the catch-all: IFS has no "otherwise" argument, so a condition that's always true plays that role. In Excel 2016 and older, which don't have IFS:

=IF(H2>=10000, "High", IF(H2>=2000, "Medium", "Low"))

Why the order matters: with the conditions reversed,

=IF(H2>=2000, "Medium", IF(H2>=10000, "High", "Low"))

SO-1009, worth ₹11,397, is labelled Medium: it passes the first test and Excel never reaches the second. No order can ever be High.

The results at the edges

A tidier alternative for many bands: put the thresholds in a small table (0 → Low, 2,000 → Medium, 10,000 → High) and use an approximate-match lookup, such as =XLOOKUP(H2, $N$2:$N$4, $O$2:$O$4, , -1), where -1 means "exact match, or the next smaller value". Changing a threshold then means editing a cell, not every formula.

Follow-ups

• What if Sales Amount is blank or text? A blank counts as 0 and gets "Low", which may hide missing data; guard against it with =IF(H2="", "", IFS(H2>=10000, "High", H2>=2000, "Medium", TRUE, "Low")).
• How would you count orders per label? =COUNTIF(K:K, "High"), or a pivot table with Size as rows.

Steps

1. Click inside the Sales data, then Insert → PivotTable (on a new sheet).
2. Drag Order Date to Rows, Category to Columns, Sales Amount to Values (Sum).
3. Right-click any date in the pivot → Group → select Months and Years.
4. Right-click any value → Show Values As → % of Row Total.

The result: each category's share of its month

The Not found column is the P-030 order from question 10. A pivot shows data problems honestly; fix the Products table, and the column disappears on refresh.

Why grouping by month alone is wrong here: the data spans two years, so a "Mar" row would add March 2024 to March 2025. Home & Kitchen would show ₹4,290 for March instead of 2025's ₹2,694, and every share in that row would be off.

Follow-ups

• The pivot didn't change after you edited the data. Why? Pivot tables don't update automatically: use Refresh. Turning the data into an Excel Table first also makes new rows flow in on refresh.
• How would you show the amounts and the shares side by side? Add Sales Amount to Values twice, and set only the second copy to % of Row Total.

=AND($D2="South", $H2>10000)

How Excel applies it: the formula is written for the top-left cell of the selection, A2, and Excel adjusts it for every other cell as if it had been copied there. For C7, it checks $D7 and $H7: the locked columns stay put, and the row follows the cell. So all eleven cells in row 10 test the same condition and light up together.

Three versions that look right but aren't

"More than" means >. An order worth exactly ₹10,000 isn't "more than ₹10,000"; if the requirement says "₹10,000 or more", use >=.

Follow-ups

• How would you let a manager change the region and threshold? Put them in input cells and lock those completely: =AND($D2=$M$1, $H2>$M$3).
• How do you see or edit the rule later? Conditional Formatting → Manage Rules, with "Show formatting rules for: This Worksheet".

An export with problems

The flag, in F2, filled down:

=COUNTIFS($A$2:$A2, A2)>1

The range $A$2:$A2 grows as the formula goes down: it counts how many times this Order ID has appeared so far, so only the second copy of SO-1007, in row 4, is TRUE.

Rows 5 and 6 are the trap. Same customer, product, date and quantity, but different Order IDs: as far as the data says, customer C-0102 ordered pens twice that day. Running Remove Duplicates with every column ticked except Order ID would delete SO-1017, a real order. If you suspect a double-click on the website, check with the order system; don't decide by deleting.

Follow-ups

• How would you flag rows duplicated across several columns? Add each column pair to COUNTIFS: =COUNTIFS($C$2:$C$6, C2, $D$2:$D$6, D2, $B$2:$B$6, B2, $E$2:$E$6, E2)>1 flags rows 2, 4, 5 and 6, which is exactly why the key matters.
• Why keep a backup? Remove Duplicates can only be undone straight away with Ctrl+Z; once the file is saved and closed, the deleted rows are gone.

The steps Power Query writes for you, in its M language:

Merged   = Table.NestedJoin(Sales, {"Product ID"}, Products, {"Product ID"}, "Products", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Products", {"Category", "Unit Cost"}),
Combined = Table.Combine({Sales_2024, Sales_2025})

Join kinds in Merge

Follow-ups

• Merge in Power Query or a relationship in the model? Usually a relationship (question 17): it keeps tables separate and the model smaller. Merge when you truly need the columns in one table, such as for cleaning.
• How do you combine 12 monthly files from a folder? Get Data → Folder, then Combine: Power Query appends every file automatically, including new ones dropped into the folder later.

              Products (dimension)
              one row per product
                      │ 1
                      │
                      │ *
Date (dimension) 1 ── * Sales (fact) * ── 1 Customers (dimension)
one row per day         one row per order    one row per customer

Region sits in the Sales table here; in a fuller model it would move to a Regions or Customers dimension, which keeps the fact table to keys and numbers.

Star vs snowflake: in a snowflake schema, dimensions are split further: Products would link to a separate Categories table instead of holding the category name itself. That saves a little repetition but adds joins. Power BI works best with a star: fewer relationships, simpler filtering, faster DAX.

Follow-ups

• Why not one big flat table? Repeating every product's details on every order wastes memory, and it makes some calculations harder, such as counting products that had no sales.
• What is the grain of the fact table? What one row means: here, one order of one product. Agreeing the grain first prevents double counting.

Steps

1. Home → Get data → Excel workbook, tick the Sales and Products sheets, then Load (or Transform Data to clean them first).
2. Open Model view. If no line joins the tables, drag Products[Product ID] onto Sales[Product ID].
3. Double-click the line and check the settings below.

Why not Both directions? Two-way filtering lets the fact table filter the dimension, which can make filters spread in unexpected ways, create ambiguous paths once more tables are added, and slow reports down. Use it only for a specific need, and prefer handling that need in a measure.

What happens to P-030: its Sales row has no matching product, so in any visual grouped by Products[Category] its ₹1,300 appears in a row labelled (Blank). That blank row is Power BI's signal of a missing key: fix it at the source rather than hiding it.

Follow-ups

• What if the IDs don't match because of spaces or letter case? Clean them in Power Query first (Trim, Uppercase), because a relationship matches only identical values.
• What is an inactive relationship? A second relationship between the same tables, such as Order Date and Delivery Date both linked to a Date table. Only one can be active; a measure can use the other with USERELATIONSHIP.

Two related modes:

• Live connection connects a report to an already published Power BI semantic model (or Analysis Services), so many reports share one model; you can't change that model's data or Power Query steps from the report.
• Composite models mix both: big, fast-changing fact tables in DirectQuery, small dimension tables imported.

If you learned Tableau first: Tableau's Extract is the counterpart of Import, and Tableau's Live connection is the counterpart of DirectQuery, not of Power BI's separate Live connection mode. That's an easy mix-up in interviews.

Follow-ups

• The DirectQuery report is slow. What would you check? The source's query speed and indexes, the number of visuals on the page (each sends queries), and whether some tables could be imported in a composite model.
• How does scheduled refresh reach an on-premises database? Through an on-premises data gateway, installed on a machine that can reach the database.

Build one with DAX: Modeling → New table:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm yyyy" ),
    "Year Month", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Financial Year",
        VAR StartYear = IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ), YEAR ( [Date] ) - 1 )
        RETURN "FY " & StartYear & "-" & RIGHT ( StartYear + 1, 2 )
)

That gives one row per day for 2024 and 2025, 731 rows in all, with 15 March 2025 in "FY 2024-25" and 1 April 2025 in "FY 2025-26", matching India's April–March financial year.

Then

1. Table tools → Mark as date table, choosing the Date column. Power BI checks that it has unique, non-blank, continuous dates.
2. Relate 'Date'[Date] (one) to Sales[Order Date] (many), with single-direction filtering.
3. Sort the Month column by Year Month (Column tools → Sort by column), or "Apr 2025" sorts before "Jan 2025".
4. Use the Date table's columns, not Sales[Order Date], in visuals and slicers.

Auto date/time: by default, Power BI quietly builds a hidden date table for every date column. It's fine for quick prototypes, but it bloats the model and can't be shared across tables; turn it off in Options → Data Load once you have your own.

Follow-ups

• CALENDAR or CALENDARAUTO? CALENDAR takes explicit start and end dates; CALENDARAUTO scans the model's date columns and covers whole years automatically, which can include stray dates such as birth dates.
• Why must it cover whole years? Year-to-date and same-period-last-year calculations assume complete years; a calendar starting mid-year gives wrong comparisons.

Static roles: one per region

Because the filter is on Sales, it also limits what the Products and Date tables show through the relationships.

Dynamic: one role for everyone. Add a small RegionManagers table with each manager's email and region ([email protected] → South, and so on), then give a single role this filter on Sales:

[Region]
    = LOOKUPVALUE (
        RegionManagers[Region],
        RegionManagers[Email], USERPRINCIPALNAME ()
    )

Each user sees only the rows whose region matches their email's entry. A new manager is a new row in the mapping table, not a new role.

Test and publish

1. In Desktop, Modeling → View as, pick a role (or "Other user" with an email), and check the numbers.
2. Publish, then in the Power BI service open the semantic model's Security page and add users or groups to the role.

A catch worth mentioning: RLS applies to people who view the report. Workspace members with the Admin, Member or Contributor role can see all the data, so give managers Viewer access or share through an app.

Follow-ups

• What if a manager covers two regions? Give them two rows in the mapping table and filter with [Region] IN CALCULATETABLE ( VALUES ( RegionManagers[Region] ), RegionManagers[Email] = USERPRINCIPALNAME () ), since LOOKUPVALUE fails when it finds more than one match.
• RLS vs hiding a page? Hiding pages or visuals isn't security: the data is still in the model. RLS removes the rows from what that user can query.

-- Calculated column on Sales: one value per row, stored at refresh
Sales Amount = Sales[Quantity] * Sales[Unit Price]

-- Calculated column on Sales: a band to slice by
Size =
SWITCH (
    TRUE (),
    Sales[Sales Amount] >= 10000, "High",
    Sales[Sales Amount] >= 2000, "Medium",
    "Low"
)

-- Measures: recalculated for every cell of every visual
Total Sales = SUM ( Sales[Sales Amount] )
Average Order Value = DIVIDE ( [Total Sales], COUNTROWS ( Sales ) )

On the sample, Total Sales is ₹62,361 and Average Order Value is ₹4,157.40 overall; in a table by region, each row recalculates both for that region alone.

The classic mistake: a ratio as a column. A Margin % column computed per row and then summed or averaged in a visual gives a meaningless number. A measure, DIVIDE ( [Total Profit], [Total Sales] ), divides the totals for whatever is filtered, which is what "margin" means.

Follow-ups

• Why do measures keep the model small? They store only the formula; a column over 50 million rows stores 50 million values.
• Can a measure be used in a calculated column? Yes, and it's evaluated for the current row through context transition (question 23).

South Sales = CALCULATE ( [Total Sales], Sales[Region] = "South" )

South Sales Kept = CALCULATE ( [Total Sales], KEEPFILTERS ( Sales[Region] = "South" ) )

All Regions Sales = CALCULATE ( [Total Sales], REMOVEFILTERS ( Sales[Region] ) )

South Electronics = CALCULATE ( [Total Sales], Sales[Region] = "South", Products[Category] = "Electronics" )

In a table visual with Region on the rows

• South Sales replaces each row's region filter with "South", so every row shows the South's total.
• South Sales Kept uses KEEPFILTERS to intersect instead: "South" and "East" have no rows in common, so non-South rows are blank.
• All Regions Sales removes the region filter entirely: the grand total on every row, which is the denominator for a share of total (question 26).
• South Electronics applies two filters together: ₹22,293.

Follow-ups

• What else does CALCULATE do? It performs context transition: inside a row context, it turns the current row into filters (question 23).
• What's the difference between FILTER and a simple CALCULATE filter? Sales[Region] = "South" is shorthand for a filter on one column; FILTER iterates a table and allows any condition, such as one comparing two columns, but it's slower on large tables.

Row context: a calculated column

Sales Amount = Sales[Quantity] * Sales[Unit Price]

For each row, DAX reads that row's Quantity and Unit Price. No filtering happens; it's simply "this row".

Filter context: a measure in a visual

Total Sales = SUM ( Sales[Sales Amount] )

In a table by Region, the South cell has the filter Region = "South", so SUM sees only South's eight orders and returns ₹29,183. The same measure in a card with no filters returns ₹62,361.

Context transition: where the two meet. Add two calculated columns to the Products table:

All Sales = SUM ( Sales[Sales Amount] )

Product Sales = CALCULATE ( SUM ( Sales[Sales Amount] ) )

All Sales has a row context (the current product) but no filter context, so SUM sees every sale: ₹62,361 on every row. In Product Sales, CALCULATE converts the current row into a filter, Product ID = this product, which flows through the relationship to Sales. Referencing a measure, Product Sales = [Total Sales], does the same, because every measure is wrapped in an implicit CALCULATE. The seven products add up to ₹61,061; the missing ₹1,300 is the P-030 order, which belongs to no product row.

Follow-ups

• Which functions create a row context? Calculated columns and iterators: SUMX, AVERAGEX, COUNTX, FILTER, ADDCOLUMNS and other functions ending in X.
• Why does context transition matter for performance? Inside an iterator over a large table, a measure triggers context transition on every row, which can be slow; iterate over a smaller table, such as VALUES of a column, where possible.

Total Sales = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

What goes wrong without an iterator

Wrong Sales = SUM ( Sales[Quantity] ) * SUM ( Sales[Unit Price] )

Adding up the unit prices of different products means nothing, so the second number is nonsense, not just "a bit off".

SUMX or a calculated column? Both work: a Sales Amount column plus SUM ( Sales[Sales Amount] ) gives the same ₹62,361. SUMX avoids storing an extra column; the column is handy if you also want to filter or band by order value (question 21).

Other iterators follow the same pattern: AVERAGEX, MINX, MAXX and COUNTX evaluate an expression per row, then aggregate. For example, the profit including cost from Products:

Total Profit = SUMX ( Sales, Sales[Quantity] * ( Sales[Unit Price] - RELATED ( Products[Unit Cost] ) ) )

RELATED fetches the matching Products row's cost through the relationship. On the sample this gives ₹18,196, but ₹1,300 of that is the P-030 order: with no matching product, RELATED returns blank, so its whole price counts as profit. Excluding it gives ₹16,896, the figure used in question 29; a careful measure would exclude or flag such rows.

Follow-ups

• Is SUMX slow? Not for simple row expressions: the engine optimises them well. It becomes costly when the expression calls measures, which triggers context transition on every row.
• AVERAGE vs AVERAGEX? AVERAGE averages one column; AVERAGEX averages an expression, such as AVERAGEX ( Sales, Sales[Quantity] * Sales[Unit Price] ), the average order value.

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

Sales FYTD = TOTALYTD ( [Total Sales], 'Date'[Date], "3/31" )

In a table by month

The financial year 2024-25 started in April 2024, but the sample has no orders from April to December 2024, so the two running totals agree until March. In April 2025 the financial year restarts, and FYTD drops back to April's own sales.

The year-end date is a text date in the model's locale. "3/31" is read as month/day on US settings; on Indian or British settings it may need to be "31/3". Check the result for April, which must restart.

The same thing written with CALCULATE, which is easier to extend:

Sales FYTD = CALCULATE ( [Total Sales], DATESYTD ( 'Date'[Date], "3/31" ) )

Why the date table matters: TOTALYTD needs a continuous calendar to work out "from the start of the year to the last date in this cell". With Sales[Order Date] instead, months without orders break the logic.

Follow-ups

• How would you compare with the same period last year? Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ), then a growth measure, DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] ).
• Quarter-to-date and month-to-date? TOTALQTD and TOTALMTD work the same way.

Category Share =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Products[Category] ) )
)

Which "remove filters" to use

With a Region slicer set to South, the first version shows each category's share of the South's sales, which is usually what a regional manager expects.

In Tableau, this is a quick table calculation, Percent of Total, and the classic slip is its Compute Using setting: computed across instead of down the table, each cell becomes a share of the wrong total.

Follow-ups

• Why DIVIDE instead of /? DIVIDE handles division by zero by returning blank (or a value you choose), so visuals don't show errors.
• How would you show the share of the parent category in a product-level matrix? Remove the filter on Product Name only: CALCULATE ( [Total Sales], REMOVEFILTERS ( Products[Product Name] ) ) as the denominator.

When a pie chart fails

• Many slices: past four or five, the small ones are unreadable and need a legend lookup.
• Similar sizes: 24% and 27% look identical as angles but are easy to compare as bars.
• Comparisons across pies: "did Electronics' share grow from 2024 to 2025?" is nearly impossible to read from two pies; use a 100% stacked bar or a line.
• Parts that aren't a whole: survey questions where people could pick several answers add up to more than 100%.

General rules that score points: start bar axes at zero; sort bars by value unless the order means something (months); one message per chart, stated in its title ("Electronics drove March's growth"); use colour to highlight, not to decorate.

Follow-ups

• Is a donut chart better than a pie? It's the same idea with a hole; it has the same weaknesses, though the centre can hold a total.
• When would you use a dual-axis chart? Rarely, and carefully: two scales can suggest relationships that aren't there. Two aligned charts are usually clearer.

Questions to ask the sales head first

• What decisions will this support: stock, promotions, staffing?
• Which numbers do you report upwards, and against what target?
• How often will you look at it, and on what device?

A layout, read like a page

┌──────────────────────────────────────────────────────────────────┐
│ South region sales                          [Month ▾] [Category ▾] │
├────────────────┬────────────────┬───────────────┬────────────────┤
│ Sales          │ vs last year   │ Orders        │ Avg order value│
│ ₹38.2 lakh     │ ▲ 6%           │ 1,040         │ ₹3,673         │
│ 92% of target  │                │               │ Margin 31%     │
├────────────────┴────────────────┴───────────────┴────────────────┤
│ Monthly sales: this year vs last year (line chart)               │
├────────────────────────────────┬─────────────────────────────────┤
│ Sales and margin by category   │ Top 10 products (bar chart)     │
│ (bar chart)                    │                                 │
├────────────────────────────────┴─────────────────────────────────┤
│ Repeat-customer rate by month (line chart)                       │
└──────────────────────────────────────────────────────────────────┘

The figures are placeholders for the full file. Each KPI card shows a comparison, because "₹38.2 lakh" alone doesn't say whether that's good.

Follow-ups

• How would you check it works for the sales head? Sit with them for ten minutes and watch them use it: which questions they still ask you shows what's missing.
• What would you leave off? Anything they can't act on, and any chart that repeats another.

The numbers behind it, from the sample's known products

A structure for the conversation

1. Headline: "Electronics is our biggest seller: 69% of sales. But at an 18% margin, it brings 45% of profit."
2. Evidence: one chart, two bars per category: share of sales and share of profit. The gap is the story.
3. What it means: each ₹100 of Electronics earns ₹18; each ₹100 of Home & Kitchen earns ₹46.
4. Recommendation: keep Electronics as the traffic driver, but reduce discount depth, bundle accessories with better margins, and pay incentives on profit, not revenue.
5. Next step: a one-month test of the new discount level in one region.

Prepare for pushback

The general form, "How do you explain findings to non-technical people?", has the same answer: start with the conclusion, use their words (₹, margin, customers), one message per chart, and end with what to do.

Follow-ups

• What if they still disagree? Propose a small test with an agreed success measure, and let the results decide.
• Would you present this in a group meeting first? No: show it to the sales head privately first, so they aren't surprised in front of others and can help shape the recommendation.

=AVERAGE(H2:H16)
=MEDIAN(H2:H16)
=COUNTIF(D2:D16, "South")

These return 4,157.4, 2,694 and 8: South has 8 of the 15 orders, more than any other region. MODE.SNGL(H2:H16) returns #N/A, since no two orders have the same value; for continuous numbers, a mode is rarely useful.

The classic salary example: nine employees earn between ₹6 lakh and ₹9 lakh a year, and the founder earns ₹1.2 crore. The mean salary is ₹18.75 lakh, more than anyone except the founder earns; the median, ₹7.75 lakh, describes a typical employee.

Follow-ups

• Which should a "typical order value" KPI use? The median, or report both: the mean for revenue planning, the median for what a typical customer spends.
• When are the mean and median equal? In symmetric distributions, such as the normal distribution (question 32).

Two stores, five days each

Same average, very different businesses: Store A needs the same staff every day; Store B needs flexible staffing and more safety stock.

Store A by hand: distances from 50 are -2, 0, 2, -1 and 1; squared, 4, 0, 4, 1 and 1, which add up to 10. Divided by n − 1 = 4, the variance is 2.5; its square root, the standard deviation, is 1.58.

=VAR.S(B2:B6)
=STDEV.S(B2:B6)
=STDEV.P(B2:B6)

For Store B, these return 562.5, 23.72 and 21.21.

Sample or population? STDEV.S divides by n − 1 and is right when your data is a sample of a bigger population, as it almost always is: five days stand in for all days. STDEV.P divides by n and assumes you have every value. A sample's values sit closer to their own mean than to the true mean, so dividing by n would understate the spread.

Follow-ups

• How do you compare spread between things on different scales? The coefficient of variation, standard deviation ÷ mean: Store B's is 23.72 ÷ 50 ≈ 47%, Store A's about 3%.
• What does a standard deviation of zero mean? Every value is identical.

The 68–95–99.7 rule, on delivery times with a mean of 30 minutes and a standard deviation of 5:

=NORM.DIST(35, 30, 5, TRUE) - NORM.DIST(25, 30, 5, TRUE)

returns 0.683, the share between 25 and 35 minutes, if delivery times really are normal.

The sample's order values are right-skewed:

₹0–2k     ██████  6
₹2–4k     ████    4
₹4–6k     ██      2
₹6–8k             0
₹8–10k    █       1
₹10–12k   ██      2

Most orders sit on the left, with a tail stretching right. The signs: the mean (₹4,157) is above the median (₹2,694), and =SKEW(H2:H16) returns 1.08; positive values mean a right skew, negative a left skew.

What right skew changes in practice

• Report the median and percentiles, not just the mean: =PERCENTILE.INC(H2:H16, 0.9) gives ₹10,111, so 90% of orders are below about ₹10,100.
• "Mean ± 2 SD" ranges mislead: for these orders, the lower end would be negative.
• Charts often read better on a log scale, and some statistical tests assume normality, so check before using them.

Follow-ups

• What would left skew look like? A long tail on the left: exam scores on an easy test, where most students score high and a few score very low. The mean then sits below the median.
• Why does the normal distribution matter so much? Many measurements are roughly normal, and averages of large samples are approximately normal even when the data isn't, which is what makes p-values for A/B tests work.

The hypotheses

• Null (H₀): the new checkout converts at the same rate as the old one.
• Alternative (H₁): the conversion rates differ.

The numbers: old checkout, 400 orders from 10,000 users; new checkout, 460 from 10,000. With those in B1, B2, C1 and C2:

In words: if the new checkout truly made no difference, a gap of 0.6 percentage points or more would turn up by chance only about 3.6% of the time. That's below 5%, so we reject the null.

What a p-value is not

Follow-ups

• What does a 95% confidence interval add? A range of plausible sizes for the effect, here roughly 0.04 to 1.16 percentage points: significant, but possibly small.
• Why 0.05? It's a convention balancing false positives and false negatives (question 34); high-stakes decisions often use 0.01.

Business examples, and which costs more

The trade-off: for a fixed sample size, lowering α to cut false positives raises β, so more real effects are missed. The way to reduce both is a bigger sample, which is why A/B tests calculate sample size first (question 36).

Follow-ups

• What is statistical power? The probability of detecting an effect that really exists, 1 − β. Low power means real improvements often go unnoticed.
• Which error does a 0.01 significance level guard against? Type I: fewer false positives, at the cost of needing more data to keep the same power.

=CORREL(B2:B7, C2:C7)

returns 0.998, an almost perfect correlation. But October and November are Dussehra and Diwali months: the company spends more on ads because it expects higher demand, and demand would have risen anyway. Reading the correlation as "every ₹1 lakh of ads brings ₹4 lakh of sales" would badly overestimate what ads achieve.

Three ways a correlation misleads

How to test for cause

• A/B test: randomly show the ad to half the users; random assignment removes confounders.
• Holdout region: pause ads in a comparable city for a few weeks and compare the change with cities that kept them.
• Before and after, against a control: compare the change in the test group with the change in a similar group over the same period (difference-in-differences).

Follow-ups

• Does a correlation near zero mean no relationship? No: correlation measures only straight-line relationships. A U-shaped pattern can have a correlation of zero.
• What correlation counts as strong? Roughly, above 0.7 or below -0.7, but strength says nothing about cause.

The design

Sample size. For 4.0% versus 4.5%, at 5% significance and 80% power:

=(NORM.S.INV(0.975)+NORM.S.INV(0.8))^2*(0.04*0.96+0.045*0.955)/(0.045-0.04)^2

That gives 25,548.1, which rounds up to 25,549 users per group, 51,098 in total; sample sizes always round up. With 6,000 checkout visitors a day, that's 8.5 days, so I'd run two full weeks: whole weeks average out weekday and weekend behaviour, and the duration is fixed before starting.

Before declaring a winner

1. Sample ratio check: a 50/50 split of 52,000 users should give about 26,000 each. A result of 26,800 against 25,200 is very unlikely by chance (p ≈ 0.000000000002), which means the assignment or tracking is broken and the results can't be trusted.
2. Full duration, no peeking: decide only at the planned end, not the first day p dips below 0.05.
3. Significance and size: p below 0.05, and a lift large enough to be worth building and maintaining.
4. Guardrails: conversion up but payment failures also up isn't a win.
5. Sense-check segments, such as new vs returning users or Android vs iOS, without slicing until something looks significant.
6. After launch, keep watching: novelty can fade.

Follow-ups

• Why randomise by user, not by session? A user who sees both checkouts on different visits contaminates both groups, and their behaviour is counted twice.
• What if the test is inconclusive? Report that the test couldn't detect a lift of 0.5 points or more; the change may still help a little. Decide on cost and design grounds, or run a bigger test.

Step 1: the headline number. On a Customers sheet, with the eight customer IDs in A2:A9 (from =UNIQUE(Sales!C2:C16)):

=COUNTIF(Sales!$C$2:$C$16, A2)
=COUNTIF(B2:B9, ">=2")/COUNTA(A2:A9)

The first, in B2 and filled down, counts each customer's orders; the second gives the repeat rate: 62.5%, or 5 of 8. In this sample, the founder's belief is wrong, though eight customers prove little; the full file decides.

Step 2: label every order with its cohort. Three helper columns on the Sales sheet, filled down:

=MINIFS($B$2:$B$16, $C$2:$C$16, C2)
=TEXT(Q2, "yyyy-mm")
=(YEAR(B2)-YEAR(Q2))*12 + MONTH(B2)-MONTH(Q2)

Column Q is the customer's first order date, R their cohort month, and S the months since their first order. For SO-1003, customer C-0457's second order, these give 14-Mar-2024, "2024-03" and 10.

Step 3: pivot it. Cohort (R) in Rows, Months since (S) in Columns, and Customer ID in Values as a Distinct Count, which needs "Add this data to the Data Model" ticked when creating the pivot. Divide each row by its month-0 count to get percentages.

What the table looks like on the full file (illustrative numbers):

Reading down the "month 1" column, newer cohorts return less often, falling from 22% to 15%. The founder is right to worry, about the trend if not the claim, and the next step is to ask what changed for newer customers: their acquisition channel, first product or delivery experience.

Define "ordered again" before counting: a second order in a later calendar month, or within 30 days of the first? Customer C-0520 ordered twice in March 2025; under the first definition, that isn't a repeat in a later month.

Follow-ups

• What's the difference between retention and churn? Retention is the share who come back; churn is the share who don't. For one-off purchases, "repeat rate" is the clearer term.
• How would you do this in Power BI or SQL? The same three steps: first order date per customer, months since it for every order, then a distinct count of customers by cohort and month.

Step 1: rule out a data problem. Compare daily totals with the source system, look for a day with zero sales (a failed load), and confirm no filter or definition changed.

Step 2: context. August had an Independence Day sale; last year, September was also about 8% below August. So part of the drop is normal, but not 20%.

Step 3: decompose the metric

Traffic barely moved and order values held steady: conversion fell. People came, but fewer bought.

Step 4: segment until the drop is located

The South explains nearly three-quarters of the drop. Splitting the South by platform shows Android app orders falling sharply from 9 September, the day an app update shipped; payment error logs then confirm UPI payments were failing on that version. Now there's a cause to fix.

A checklist of usual suspects: data and tracking problems, seasonality and festival dates, price changes, stock-outs of best-sellers, marketing campaigns ending, website or app errors, delivery problems, competitor sales.

Follow-ups

• What would you present? The cause, the evidence (conversion fell in South Android after the 9 September update), the estimated revenue lost, and the fix, in that order.
• What if no single segment explains it? Then the cause is broad, such as seasonality, a price change or a market-wide slowdown, and the comparison with last year and with competitors matters most.

The numbers (₹ lakh)

• Revenue view: the sale added ₹60 lakh, but the following days fell ₹20 lakh short of normal as customers who had bought early didn't return yet. Net incremental revenue: +₹40 lakh.
• Profit view: discounts cut the margin to 18%, so the sale days earned only ₹1.2 lakh more profit than normal, and the dip afterwards cost ₹6 lakh. Net incremental profit: -₹4.8 lakh.

So did it work? Revenue grew, profit fell. The sale pays off only if its new customers bring more than ₹4.8 lakh of future profit: with 9,000 new customers, about ₹53 each. That's the question to answer next, using their repeat rate (question 37).

How to set the baseline fairly

• Use comparable normal days, adjusted for the usual festive lift, not the quietest weeks of the year.
• Compare with last year's sale lined up by days from Diwali, since Diwali moves between October and November.
• If possible, hold out a region or a customer group from the sale as a control.

Other checks: stock-outs during the sale (lost sales), returns and cancellations after it, payment and delivery failures, and whether discounts went to customers who would have paid full price anyway.

Follow-ups

• What's cannibalisation? Sale sales that would have happened anyway, at full price, now earning less; the post-sale dip is one sign of it.
• How would you design next year's sale better? Test discount levels in different regions, target discounts at new or lapsed customers, and track profit, not revenue, as the success metric.

Clarify first: all platforms or one? Food only, or groceries too? A typical weekday or weekend? Here: all food-delivery apps, an average day.

Top-down, from people

Bottom-up, from supply: about 50,000 delivery riders active on a typical day, each completing about 16 deliveries, gives 8 lakh orders a day. Two independent routes landing close together makes the estimate more believable.

What interviewers score

• Structure: a clear chain from population to orders, written down as you go.
• Stated assumptions, with a reason for each, even if the numbers are rough.
• A sanity check: about 0.19 orders per user per day, or 1.35 a week, sounds plausible for a city like Bengaluru.
• Sensitivity: say which assumption matters most. Here, the share and frequency of heavy users: if heavy users order only twice a week, the total falls to about 5.7 lakh a day.

Follow-ups

• How would you get a better number? Published order volumes from food-delivery companies' results, scaled by Bengaluru's share of their orders, or app-download and usage data.
• Would weekends differ? Yes: weekend dinner orders run higher, so a "typical day" is an average of a busier weekend and quieter weekdays.