Webbo3 Power BI · Cohort 1 · Day 11 of 16 · Lab

Day 11 Lab: CALCULATE Together

Yesterday you learned what CALCULATE does. Today the class builds the measures a manager actually asks for, and checks every one of them.

A manager never asks for "CALCULATE". She asks: "Which station matters most? Did we grow? How do people pay?" Each question below is one of hers, and each answer is one or two measures.

Same lab rules as Day 4 and Day 9: every number on the checkpoint tables must match your laptop before the class moves on. If yours differs, check the measure first, then the relationships.

1. "Which Station Matters Most?" Share of Total

Definition

A share of total is one part divided by the whole: this station's revenue divided by every station's revenue. The part is [Total Revenue] as the row filters it. The whole is the same measure with the station filter removed, which is exactly what ALL ( Station_Master ) does.

Real-Life Example

Your class scores 400 marks between you and you scored 40. Your share is 40 out of 400, 10%. To work it out you need your own score and the class total at the same time. ALL is how a measure sees the class total while it is standing on your row.

1
2
3
4
5
6
7

// The whole: revenue with every station filter removed
All Stations Revenue =
CALCULATE ( [Total Revenue], ALL ( Station_Master ) )

// The part divided by the whole
% of All Stations =
DIVIDE ( [Total Revenue], [All Stations Revenue] )

GRA Filling Station earns 14.8% of everything. The total row says 100%, which is your proof the measure is right: the whole divided by the whole.

2. "Did We Grow?" One Year Against Another

Definition

A year measure is Total Revenue with the year fixed by CALCULATE. Two of them side by side, and a third that divides the difference by the older year, give a growth percentage.

Real-Life Example

Last year you sold 100 loaves a week; this year 120. You grew by 20, and 20 out of the old 100 is 20%. Always divide by the old number: that is what you grew from.

The table is called Date, which is also the name of a DAX function, so Power BI writes it in single quotes: 'Date'[Year]. Type it that way yourself and the formula bar will not complain.

1
2
3
4
5
6
7
8
9
10

// Revenue with the year fixed to 2023, and to 2024
Revenue 2023 =
CALCULATE ( [Total Revenue], 'Date'[Year] = 2023 )

Revenue 2024 =
CALCULATE ( [Total Revenue], 'Date'[Year] = 2024 )

// The change, divided by the older year
Growth % =
DIVIDE ( [Revenue 2024] - [Revenue 2023], [Revenue 2023] )

Independence Layout Filling Station grew fastest and Lekki Filling Station slowest. The year filter is on the Date table and the rows are on Station_Master: different columns, so both filters apply, as Day 10's rule says.

3. "How Do People Pay?" A Share Inside Each Row

Definition

This time the whole is not the business, it is the row. Cash revenue at Lekki divided by all revenue at Lekki. No ALL is needed: [Total Revenue] on the row is already "all payment methods at this station".

Real-Life Example

Asking each shop on a street what fraction of today's takings came in cash. Each shop divides by its own takings, not the street's.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

// Cash sales only
Cash Revenue =
CALCULATE ( [Total Revenue], Sales[PaymentMethod] = "Cash" )

// Cash as a share of this row's revenue
Cash Share =
DIVIDE ( [Cash Revenue], [Total Revenue] )

// The same two steps for POS, written as one measure
POS Share =
DIVIDE (
    CALCULATE ( [Total Revenue], Sales[PaymentMethod] = "POS" ),
    [Total Revenue]
)

POS Share shows that a CALCULATE can sit directly inside DIVIDE. Both styles are correct. The two-measure style is easier to check, because Cash Revenue can go on a card by itself.

4. "How Many Litres?" CALCULATE on a Different Measure

CALCULATE works on any expression, not only revenue. The operations manager thinks in litres.

1
2
3
4
5
6

// Litres of petrol: add Quantity, only for the petrol product
Petrol Litres =
CALCULATE (
    SUM ( Sales[Quantity] ),
    Product_Master[ProductName] = "PMS (Petrol)"
)

Litres grew -5.9% while revenue grew 55.6%. Most of the growth was the price, not more petrol, the same finding as Day 9, now proved in litres.

Summing Quantity across products would add litres of fuel to tins of engine oil, which means nothing. That is why this measure is filtered to one product.

5. "Which Stations Are Dead Weight?" Revenue Against Target

Definition

A target measure works out how much a station should have earned in whatever period the reader is looking at. Station_Master holds a yearly target per station (Day 5), so the target for any period is the yearly figure times the number of days in that period, divided by 365.

Real-Life Example

A savings goal of ₦365,000 a year is ₦1,000 a day. Ask how you are doing after 30 days and the fair comparison is ₦30,000, not the whole year's goal.

COUNTROWS ( 'Date' ) counts the days in the current filter: 365 for 2023, 366 for 2024, 731 for both. That is why the Date table must have every day, not only days with sales.

1
2
3
4
5
6
7
8
9
10

// The yearly targets of the stations in the filter, scaled to its days
Target =
DIVIDE (
    SUM ( Station_Master[AnnualRevenueTarget_NGN] ) * COUNTROWS ( 'Date' ),
    365
)

// How much of the target was reached
% of Target =
DIVIDE ( [Total Revenue], [Target] )

In 2024, 3 stations beat their target and 4 fell short. The three with the biggest targets are not the ones beating them: a big target is a big expectation, not a big result. That is the first answer to Alhaji Musa Bello's question about dead weight, and it is more careful than "which station earns least".

6. The Solo Project Is Released

Today you get the Solo Project: PayLink Nigeria, a mobile money network of 41 agents in 10 states. You will clean, model, measure and build its dashboard entirely on your own. It is due on Day 14.

Do This Now

1. Build All Stations Revenue and % of All Stations, and check the total row says 100%.
2. Build Target and % of Target, set a Year slicer to 2024, and check the table.
3. Build Revenue 2023, Revenue 2024 and Growth %, then the growth-by-station table.
4. Build Cash Revenue, Cash Share and POS Share, and the payment table.
5. Build Petrol Litres by year.
6. Do the Try This.
7. Download the PayLink dataset and read the Solo Project brief.

Checkpoint

Next: the Solo Project brief, then Day 12, time intelligence.