Agriculture · Intermediate
Model a messy Kenyan farm-yield export in Power BI and answer 60 DAX questions on the way to a farmer-performance dashboard.
You are the business intelligence analyst for a county agricultural extension programme. Field officers have compiled a season's records for 500 smallholder farmers across Kenya: what they planted, where, on how many acres, what it yielded, what it sold for and what it cost. The export is exactly as it left the field app, with "Error", "N/A" and blank cells scattered through the categorical columns. The programme director wants a Power BI report that shows which crops, counties and practices make money, and the analytics team wants you to demonstrate command of DAX along the way: math and statistical functions, filter context, logical and text functions, and time intelligence over a proper date table.
Your role
Business Intelligence Analyst, county agricultural extension programme
The programme cannot tell which crops and counties generate the most revenue and profit, whether irrigation, fertiliser or pest control practices pay off, or how revenue moves through the year. The field export is too dirty to trust as-is, and no measures exist to answer any of these questions consistently.
Import the CSV, set every column's type, and deal with the "Error", "N/A" and blank cells in Season, Crop Variety, Soil Type, Irrigation Method, Fertilizer Used, Pest Control and Weather Impact. Convert the planting and harvest dates, and sanity-check Revenue and Profit against Yield × Market Price and Cost of Production.
Create a date table for 2023 with CALENDAR, mark it as a date table, relate it to Planting Date, and decide which columns are dimensions and which are facts. Add a Region column (West, East, Central) from County so the filter and SWITCH questions have something to work with.
One table of 500 farmer-season records with 20 columns: farmer name and contact, county, crop type and variety, season, planted area in acres, yield in kilograms, market price and revenue in KES, cost of production and profit in KES, planting and harvest dates, soil type, irrigation method, fertiliser used, pest control, weather impact and free-text notes. Every categorical column contains a mix of "Error", "N/A" and empty cells that must be handled in Power Query before modelling, and the revenue and profit columns should be checked against yield and price rather than trusted.
How to do this project
The scenario
Objectives
Tasks and business questions
Dataset and data dictionarykenya_crops500 farmer-season records with yields, prices, revenue, costs, dates and farming practices, including the Error, N/A and blank cells to clean. · 500 rows
| Column |
|---|
Write the core measures the report depends on.
Control filter context with CALCULATE and its modifiers.
Classify rows and guard calculations.
Work the farmer, crop, county and market fields with DAX text functions.
Use the date table to reason about time.
Assemble the measures into a report: KPI cards for total revenue, profit, yield and farmers; revenue and profit by crop and county; the effect of irrigation, fertiliser and pest control on profit; and revenue over time. Add slicers for county, crop type and season, then write three insights for the programme director.
| Type |
|---|
| Description |
|---|
| Example |
|---|
Farmer Name | text | Anonymised farmer label. | Farmer 1 |
County | text | Kenyan county where the plot is located. | Kiambu |
Crop Type | text | Crop grown this season. | Potatoes |
Crop Variety | text | Organic, Local or Hybrid. Contains Error, N/A and blanks. | Organic |
Season | text | Long Rains, Short Rains or Dry Season. Contains Error, N/A and blanks. | Long Rains |
Planted Area (Acres) | decimal | Area planted, in acres. | 15.14 |
Yield (Kg) | decimal | Harvest weight in kilograms. | 1654.76 |
Market Price (KES/Kg) | decimal | Selling price per kilogram in Kenyan shillings. | 100.19 |
Revenue (KES) | decimal | Reported revenue. Check it against yield × price. | 2510066.72 |
Cost of Production (KES) | decimal | Reported cost of production for the plot. | 8977.19 |
Profit (KES) | decimal | Reported profit. Check it against revenue − cost. | 2501089.53 |
Planting Date | date | Date planted, ISO format. | 2023-08-01 |
Harvest Date | date | Date harvested, ISO format. | 2023-11-02 |
Soil Type | text | Clay, Peaty, Silty, Sandy or Loam. Contains Error, N/A and blanks. | Peaty |
Irrigation Method | text | None, Drip, Flood or Sprinkler. Contains Error, N/A and blanks. | Drip |
Fertilizer Used | text | None, DAP, Manure, CAN or UREA. Contains Error, N/A and blanks. | DAP |
Pest Control | text | None, Chemical or Organic. Contains Error, N/A and blanks. | Organic |
Weather Impact | text | None, Mild or Severe. Contains Error, N/A and blanks. | Mild |
Farmer Contact | text | Phone number as text; keep the leading zero. | 0731810617 |
Notes | text | Free-text field officer remark; blank for most rows. | High yield |
Error, N/A and blank values handled deliberately in Power Query with the choices documented; a date table created with CALENDAR and marked as a date table; each DAX practice question answered with a working formula that uses the function named; measures that respect and override filter context correctly; and a report a programme director can filter by crop, county and season and read unaided.
Recommended tools
Skills covered
Power BI file (.pbix) with the Power Query cleaning steps, the data model, a marked date table and every DAX measure and calculated column from the practice questions