Why Confidence Without Power BI Skills Still Fails

-- Calculated column (row level)
Row Margin % = DIVIDE ( Sales[Profit], Sales[Revenue] )

-- Confident but wrong card measure
Margin % (Wrong) = AVERAGE ( Sales[Row Margin %] )

Why it is wrong:
Order A: revenue 100,   profit 50   -> 50%
Order B: revenue 10000, profit 1000 -> 10%
Average of rates: 30%
Real margin: 1050 / 10100 = about 10.4%

Small orders get the same weight as large ones,
so the card overstates the business margin.

Total Revenue = SUM ( Sales[Revenue] )
Total Profit  = SUM ( Sales[Profit] )

Margin % =
DIVIDE ( [Total Profit], [Total Revenue] )

What to say when asked:
'I build ratios from totals, not by averaging
row-level ratios. DIVIDE handles a zero
denominator by returning blank instead of an
error. Because it is a measure, it recalculates
for every region, month or product the user
selects.'

Check: put Margin % in a table by Region and
confirm one row by hand from the source totals.

-- Sales has one row per order LINE, not per order

Order Lines   = COUNTROWS ( Sales )
Orders        = DISTINCTCOUNT ( Sales[OrderID] )
Customers     = DISTINCTCOUNT ( Sales[CustomerID] )

Avg Order Value =
DIVIDE ( SUM ( Sales[LineAmount] ), [Orders] )

-- Averaging lines would give average LINE value,
-- which is lower whenever orders have many lines.

Repeat Customers =
COUNTROWS (
    FILTER (
        VALUES ( Sales[CustomerID] ),
        CALCULATE ( DISTINCTCOUNT ( Sales[OrderID] ) ) >= 2
    )
)

Repeat Rate =
DIVIDE ( [Repeat Customers], [Customers] )

How it works, in words:
'VALUES gives the customers visible in the
current filter context. For each one, CALCULATE
turns the row into a filter and counts their
orders. FILTER keeps those with two or more,
and COUNTROWS counts them.'

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month No", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year Month", FORMAT ( [Date], "YYYY-MM" )
)

Steps after creating it:
1. Table tools -> Mark as date table -> Date column
2. Relate Date[Date] to Sales[OrderDate], one-to-many
3. Sort Month by Month No
4. Use Date fields, not Sales[OrderDate], in visuals

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

Need: a Product slicer that shows only products
the selected customer has bought.

Instead of setting the relationship to Both
permanently, apply it inside one measure:

Customer Products =
CALCULATE (
    DISTINCTCOUNT ( 'Product'[ProductID] ),
    CROSSFILTER ( Sales[ProductID], 'Product'[ProductID], BOTH )
)

What to say:
'I keep relationships single-direction by default.
When one calculation needs a filter to flow the
other way, I use CROSSFILTER inside that measure,
so the rest of the model stays predictable.'

FOR EVERY CARD AND KEY VISUAL

Number: _________________
Measure name: ___________  (not implicit)
Grain of the fact table: one row per ______
[ ] Ratios built from totals with DIVIDE
[ ] Counts use DISTINCTCOUNT where needed
[ ] Checked against source for one filter
[ ] Correct with two slicer values selected
[ ] Correct with no slicer values selected
[ ] Time measures use a marked Date table

Fail any box -> fix before the interview.

MODEL VIEW CHECK

[ ] Fact table(s) and dimension tables separated
[ ] One-to-many relationships, dimension -> fact
[ ] No unnecessary both-direction filters
[ ] No inactive relationships I cannot explain
[ ] Auto date/time turned off
[ ] Date table marked and related
[ ] Unused columns removed
[ ] Measures in a dedicated measures table

Be ready to explain any box you did NOT tick.

90-SECOND REPORT PITCH

1. Business question (1 sentence)
   'Which regions and categories drive margin?'
2. Data and model (2 sentences)
   'Sales fact at order-line grain, with Date,
    Product, Customer and Region dimensions.'
3. Two key measures and why they are correct
   'Margin % divides total profit by total revenue.'
4. One problem you solved
   'Customer counts were inflated; I switched to
    DISTINCTCOUNT and checked against source.'
5. One insight a manager would act on

End with: 'Shall I build one of these measures live?'