DSEAfrica

Agriculture · Intermediate

Kenya Crops Power BI Dashboard and DAX Practice

Model a messy Kenyan farm-yield export in Power BI and answer 60 DAX questions on the way to a farmer-performance dashboard.

Start this project

The scenario

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 business problem

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.

Objectives

  • 1Load the export with Power Query and clean the Error, N/A and blank values, dates and numeric types.
  • 2Build a data model with a proper date table marked for time intelligence.
  • 3Write DAX measures with SUM, SUMX, AVERAGE, AVERAGEX and MEDIAN for revenue, cost, profit and yield.
  • 4Use CALCULATE with FILTER, ALL, ALLEXCEPT, REMOVEFILTERS and HASONEVALUE to control filter context.
  • 5Apply IF, AND, OR, SWITCH and IFERROR to classify farmers and crops.
  • 6Use the text functions EXACT, FIND, FORMAT, LEFT, RIGHT, LEN, LOWER, UPPER, TRIM and CONCATENATE on farmer and crop fields.
  • 7Use CALENDAR, DATEDIFF, MONTH, YEAR, TOTALYTD, SAMEPERIODLASTYEAR, DATEADD and DATESBETWEEN for time intelligence.
  • 8Publish a dashboard that shows performance by crop, county, season and practice with slicers.

Tasks and business questions

  1. 1

    Load and clean the export in Power Query

    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.

    • How many cells hold "Error", "N/A" or nothing, and what did you replace each with and why?
    • Does Revenue (KES) equal Yield (Kg) × Market Price (KES/Kg) for every row? What did you do where it does not?
    • Which columns should be text, whole number, decimal or date?
  2. 2

    Build the model and the date table

    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.

    • Create a Date Table for 2023 using CALENDAR(). Which columns did you add to it?
    • Which relationship did you create, and why is the date table marked?
    • How did you map counties to regions?
  3. 3

    Math and statistical functions

    Write the core measures the report depends on.

    • Write a DAX measure to calculate Total Revenue from the Revenue (KES) column.
    • Using SUMX(), calculate Total Fertilizer Cost by multiplying Yield (Kg) with a fertiliser cost per kilogram.
    • Create a measure that finds the Average Market Price of crops.
    • Use AVERAGEX() to calculate the Average Profit per crop as (Yield × Price − Cost of Production).
    • Write a measure to return the Median Yield of all crops.
  4. 4

    Filter functions

    Control filter context with CALCULATE and its modifiers.

    • Use FILTER() inside CALCULATE() to get Total Revenue for crops with Revenue > 5,000 KES.
    • Write a DAX measure with CALCULATE() to find Total Revenue for crops grown in the West region.
    • Create a measure with HASONEVALUE() to check whether only one Crop Type is selected in a slicer.
    • Using ALL(), calculate the overall revenue ignoring all filters.
    • Use ALLEXCEPT() to calculate Revenue by Region, ignoring all other filters.
    • With REMOVEFILTERS(), calculate Total Revenue ignoring the Region filter only.
  5. 5

    Logical functions

    Classify rows and guard calculations.

    • Write a DAX formula with IF() to return "High" if Profit Margin > 0.2, else "Low".
    • Using AND(), calculate revenue only when Revenue > 5000 and Profit Margin > 0.2.
    • With OR(), return TRUE if either Revenue > 5000 or Profit Margin > 0.2.
    • Write a nested IF() that classifies Revenue into "High", "Medium" or "Low".
    • Use SWITCH() to return "Western Region", "Eastern Region", "Central Region" or "Unknown Region" based on Region.
    • Write a measure using IFERROR() that divides Revenue by Yield but returns 0 if there is an error.
  6. 6

    Text functions

    Work the farmer, crop, county and market fields with DAX text functions.

    • Use EXACT() to check whether a Crop Type is exactly "Potatoes" (case-sensitive), and whether Farmer Name is exactly "Mary Wanjiku".
    • Use FIND() to find the position of "John" in Farmer Name, returning 0 when it is not found, and the position of "Pot" in Crop Type.
    • Format Total Revenue as currency with FORMAT(), format Planting Date as "March 10, 2023", and format Yield (Kg) to two decimal places.
    • Extract the first 3 characters of Crop Type with LEFT(), the first 4 of County and the first 5 of Farmer Name.
    • Extract the last 5 characters of Farmer Name with RIGHT(), the last 3 of Crop Type and the last 4 of County.
    • Use LEN() to find the length of each Farmer Name, Crop Type and County.
    • Convert Crop Type to lowercase with LOWER() and Farmer Name to uppercase with UPPER().
    • Use TRIM() to remove extra spaces in Crop Type, Farmer Name and County.
    • Create a column that concatenates Farmer Name and Crop Type as "Mary Wanjiku - Potatoes".
    • Create columns for LEFT(Crop Type, 3) + County ("MAI - Nakuru") and Farmer Name + Planting Date ("John Kamau planted on March 10, 2023").
  7. 7

    Date and time intelligence

    Use the date table to reason about time.

    • Use DATEDIFF() to calculate the number of days between Planting Date and TODAY().
    • Extract the month number and year from Planting Date using MONTH() and YEAR().
    • Write a measure to calculate YTD Revenue using TOTALYTD().
    • Using SAMEPERIODLASTYEAR(), calculate Revenue Last Year for comparison.
    • Write a measure with DATEADD() to calculate Revenue 3 Months Ago.
    • Use DATESBETWEEN() to calculate Revenue only between July 1 and September 30.
  8. 8

    Build and publish the dashboard

    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.

    • Which crop and county combination is most profitable per acre, and which loses money?
    • Do irrigated or fertilised plots earn more profit than those without?
    • What three actions would you recommend to the programme director, and which measures support each?

Dataset and data dictionary

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.

Kenya crops field exportkenya_crops

500 farmer-season records with yields, prices, revenue, costs, dates and farming practices, including the Error, N/A and blank cells to clean. · 500 rows

Sign in to download
Data dictionary for Kenya crops field export
ColumnTypeDescriptionExample
Farmer NametextAnonymised farmer label.Farmer 1
CountytextKenyan county where the plot is located.Kiambu
Crop TypetextCrop grown this season.Potatoes
Crop VarietytextOrganic, Local or Hybrid. Contains Error, N/A and blanks.Organic
SeasontextLong Rains, Short Rains or Dry Season. Contains Error, N/A and blanks.Long Rains
Planted Area (Acres)decimalArea planted, in acres.15.14
Yield (Kg)decimalHarvest weight in kilograms.1654.76
Market Price (KES/Kg)decimalSelling price per kilogram in Kenyan shillings.100.19
Revenue (KES)decimalReported revenue. Check it against yield × price.2510066.72
Cost of Production (KES)decimalReported cost of production for the plot.8977.19
Profit (KES)decimalReported profit. Check it against revenue − cost.2501089.53
Planting DatedateDate planted, ISO format.2023-08-01
Harvest DatedateDate harvested, ISO format.2023-11-02
Soil TypetextClay, Peaty, Silty, Sandy or Loam. Contains Error, N/A and blanks.Peaty
Irrigation MethodtextNone, Drip, Flood or Sprinkler. Contains Error, N/A and blanks.Drip
Fertilizer UsedtextNone, DAP, Manure, CAN or UREA. Contains Error, N/A and blanks.DAP
Pest ControltextNone, Chemical or Organic. Contains Error, N/A and blanks.Organic
Weather ImpacttextNone, Mild or Severe. Contains Error, N/A and blanks.Mild
Farmer ContacttextPhone number as text; keep the leading zero.0731810617
NotestextFree-text field officer remark; blank for most rows.High yield

Required deliverables

  • 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
  • A report with pages for overview KPIs, crop and county performance, farming practices and revenue over time, all driven by slicers
  • A DAX answer sheet (Markdown or PDF) listing each practice question with the formula written and a screenshot of the result
  • Short README describing the cleaning decisions, the model and three insights for the programme director

How it is marked

  • Data cleaning15%
  • Technical accuracy20%
  • Analysis quality20%
  • Visualisation and dashboard design15%
  • Business insights15%
  • Recommendations10%
  • Documentation and presentation5%
  • Pass mark60%

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.