Workshop 2 - Accounting Arquitectures - Module 2

Author

Alberto Dorantes, Ph.D.

Published

August 13, 2026

Abstract

In this workshop we practice the basis of an ETL process, database modeling and reporting using PowerBI web version. This is a hands-on challenge in which you import a well-formed multinational food distribution dataset into the WEB version of Power BI and concentrate on what matters most: building a correct semantic model, writing correct DAX, and decomposing a change in gross profit into the drivers management can actually act on.

1 CHALLENGE: Andina Foods International

1.1 Business context

Andina Foods International (AFI) is a food and beverage distributor operating in eight countries across North and South America. It sells five product divisions - Beverages, Dairy, Snacks, Frozen Foods and Bakery - to supermarket chains, convenience stores, wholesalers, foodservice operators and e-commerce platforms.

You have just joined the Strategic Planning department reporting to the CFO. The CFO has a problem and no analyst:

“Volumes grew last year and consolidated gross profit barely moved. I cannot tell you why. Finance blames the commercial team, the commercial team blames procurement, and marketing insists their spending is efficient. I need evidence, not opinions.”

The IT department has extracted eight tables from the ERP data warehouse. This extract is well formed: keys are consistent, there are no duplicates, dates are in ISO format, and every foreign key exists in its dimension. Your effort therefore goes where it belongs in this version of the workshop: building a correct semantic model, writing correct DAX, and producing an analysis somebody can act on.

Why a clean extract does not make the audit optional. In practice you never assume an extract is clean; you verify that it is. Step 2 shows you how to verify it in a few minutes. Skipping verification is how analysts end up presenting wrong numbers to a CFO.

1.2 The data

The tables are in the dataW2.xlsx file as Sheets.

Currency convention: every monetary amount in the two fact tables is expressed in the LOCAL CURRENCY of the country. Exchange rates live in the country dimension. Nothing can be consolidated before it is converted to USD - this is the single most important modelling decision in the workshop.

1.2.1 `W1B_sales_orderlines` - FACT TABLE (~9,600 rows)

Column	Meaning
`OrderLineID`	Unique identifier of the order line (primary key)
`OrderID`	Sales order identifier; one order contains several lines
`OrderDate`	Date the order was placed (ISO format `YYYY-MM-DD`)
`CustomerID`	FK → `W1B_customers`
`ProductID`	FK → `W1B_products`
`EmployeeID`	FK → `W1B_employees`
`Units`	Units sold in this line
`UnitPriceLC`	List unit price, local currency
`UnitCostLC`	Standard unit cost at the time of the sale, local currency
`DiscountPct`	Discount granted on this line, as a decimal (`0.10` = 10%)

Note that the ERP stores the unit cost on the transaction itself. This is common in distribution systems and it is convenient: cost changes over time are already reflected line by line, so you do not need a separate cost table or a composite key.

1.2.2 `W1B_customer` - DIMENSION (external agent, 62 rows)

CustomerID, CustomerName, City, CountryCode, Channel, CreditRating

1.2.3 `W1B_products` - DIMENSION (resource, 50 rows)

ProductID, ProductName, DivisionID, SubCategory, LaunchYear

1.2.4 `W1B_divisions` - DIMENSION (5 rows)

DivisionID, DivisionName, StorageType

1.2.5 `W1B_employees` - DIMENSION (internal agent, 14 rows)

EmployeeID, FirstName, LastName, Title, Region, HireDate

1.2.6 `W1B_countries` - DIMENSION (8 rows)

CountryCode, CountryName, Region, Currency, ExchangeRate_LCperUSD, CorporateTaxRate

The exchange rate is local currency units per 1 USD, so \(USD = LocalCurrency / ExchangeRate\). The tax rate is a decimal.

1.2.7 `W1B_marketing_expenses` - SECOND FACT TABLE (~960 rows)

MonthStart, CountryCode, DivisionID, TVAds, RadioAds, MediaProduction, Promotions - all in local currency, at country × division × month granularity. Their sum is AFI’s SG&A.

1.2.8 `W1B_calendar` - DATE DIMENSION (731 rows)

Date, Year, Quarter, MonthNumber, MonthName, MonthYear, YearMonthKey - covering 2024-01-01 to 2025-12-31.

Simplification note. AFI’s SG&A here contains only selling and marketing expenses; central overhead, depreciation and interest are managed at corporate level and are outside the dataset. The “EBIT” you compute is therefore an operating contribution before corporate overhead.

1.3 Step 0 - Set up your environment

Go to https://app.powerbi.com and sign in with your institutional account (@tec.mx). Do not use a personal account.
Create a workspace: Workspaces → New workspace, named CF2003B-W2-<your matricula>.
Upload the .xlsx file to a OneDrive for Business folder (for example CF2003B/Workshop2/). Working from OneDrive means you can refresh the model later instead of re-uploading - which is the entire point of a cloud BI platform.

If your tenant does not let you create a workspace, do the whole workshop in My Workspace; if dataflows are unavailable to you, use Route B below. Both routes are accepted.

1.4 Step 1 - Import

Route A (recommended) - a Dataflow. In your workspace: New → Dataflow → Add new tables → Text/CSV, point at the OneDrive link of each file, authenticate with your organizational account, then Save & Refresh. You are now in Power Query Online: the same engine you used in Power BI Desktop, running in the browser. Then create a semantic model that reads from this dataflow.

Route B (fallback) - direct upload. In your workspace: New → Semantic model → Excel/CSV (or Get data → Files) and upload the excel. Open the semantic model and choose Edit tables / Transform data to reach the web Power Query editor.

Either way, Step 1 ends with eight tables inside one semantic model.

While still in Power Query, confirm the data type of every column. This is the one import step that is never optional:

OrderDate, MonthStart, Date, HireDate → Date
Units, Year, LaunchYear, YearMonthKey, MonthNumber → Whole number
UnitPriceLC, UnitCostLC, DiscountPct, ExchangeRate_LCperUSD, CorporateTaxRate, and the four expense columns → Decimal number
everything else → Text

A numeric column silently imported as text will not aggregate, and a date imported as text will destroy every time-intelligence calculation you write later.

1.5 Step 2 - Verify the extract and handle missing values

This dataset is well formed, but you must demonstrate that rather than assume it. Run this five-minute audit and report the result of each check - including the checks that come back clean.

1.5.1 2.1 The integrity audit

#	Check	How to run it in Power Query
1	Duplicate rows	Select all columns → Remove Duplicates. Compare the row count before and after: it must not change.
2	Duplicate keys in dimensions	On `CustomerID` and `ProductID`: Keep Duplicates. The result must be empty.
3	Column quality and distribution	Enable Column quality, Column distribution and Column profile in the View ribbon. Read the Valid / Error / Empty bars on every column.
4	Orphan foreign keys	Merge Queries `Sales` with `Customers` on `CustomerID` using a Left Anti join. An empty result means no orphans. Repeat for `ProductID` and `EmployeeID`.
5	Whitespace in keys	Apply Trim to the key columns anyway. It costs one click and it is the single most common silent cause of missing rows in a relationship.

Report these five results in your cleaning log. “I checked and found nothing” is a valid, professional finding; “I did not check” is not.

1.5.2 2.2 The missing values

The extract has exactly four kinds of blanks. Handle each one and justify your choice in one sentence, because each choice changes a number somewhere.

Where	What is missing	Suggested treatment
`Sales[DiscountPct]`	~18 blank cells	Replace with `0`. A blank discount field in this ERP means no discount was applied, not unknown. Leaving it blank makes `1 - DiscountPct` unreliable and understates net sales.
`Customers[City]`	6 blanks	Replace with `"Unknown"`. Never delete the customer - you would delete their sales too.
`Customers[Channel]`, `Customers[CreditRating]`	3 and 4 blanks	Replace with `"Not classified"`. A blank category silently disappears from slicers and legends, so your percentages stop adding to 100%.
`Marketing[RadioAds]`, `[MediaProduction]`, `[Promotions]`	10 blanks scattered	Replace with `0` before you add them together. In Power Query, `100 + null = null`, so a single blank would wipe out the whole month’s SG&A for that country and division.

1.5.3 2.3 One derived column in Power Query

In Marketing, add a custom column:

SGAE_LC = [TVAds] + [RadioAds] + [MediaProduction] + [Promotions]

(Optional but recommended: also Unpivot the four expense columns into an ExpenseType / Amount pair in a separate query. It gives you an expense-mix analysis for free in Dashboard 2)

1.6 Step 3 - Build the semantic model

Go to Model view. Every relationship below is a single-column join with 1-to-many cardinality and a single cross-filter direction, from the dimension (the one side) to the fact (the many side).

The nine relationships of the model
From (one)	To (many)	Cardinality
`Customers[CustomerID]`	`Sales[CustomerID]`	1 : N
`Products[ProductID]`	`Sales[ProductID]`	1 : N
`Employees[EmployeeID]`	`Sales[EmployeeID]`	1 : N
`Calendar[Date]`	`Sales[OrderDate]`	1 : N
`Countries[CountryCode]`	`Customers[CountryCode]`	1 : N
`Divisions[DivisionID]`	`Products[DivisionID]`	1 : N
`Countries[CountryCode]`	`Marketing[CountryCode]`	1 : N
`Divisions[DivisionID]`	`Marketing[DivisionID]`	1 : N
`Calendar[Date]`	`Marketing[MonthStart]`	1 : N

Then:

Mark Calendar as a date table (Table tools → Mark as date table, using the Date column). Without this, SAMEPERIODLASTYEAR and every other time-intelligence function is unreliable.
Hide the technical columns from report view: all the ...ID foreign keys in the fact tables, and YearMonthKey. A model that offers thirty fields when only twelve are meant to be used is a model that will be misused.
Sort MonthName by MonthNumber (Column tools → Sort by column), otherwise every chart will order the months alphabetically: April, August, December…

Three structural points worth understanding rather than just executing:

Countries and Divisions reach Sales indirectly, through Customers and Products. That branch makes the model a snowflake, and it is perfectly valid: filters propagate along the entire chain, Country → Customer → Sales.
Calendar, Countries and Divisions are conformed dimensions. They sit above both fact tables, which is precisely what allows you to put net sales and marketing spend on the same axis. Never create a second date table.
No relationship touches Sales and Marketing directly. Two fact tables never join to each other; they communicate only through the dimensions they share.

1.7 Step 4 - Calculated columns and measures

1.7.1 4.1 Calculated columns (table `Sales`)

Row-level arithmetic belongs in columns:

Year = YEAR( Sales[OrderDate] )

FXRate = RELATED( Countries[ExchangeRate_LCperUSD] )

GrossSalesUSD = Sales[Units] * Sales[UnitPriceLC] / Sales[FXRate]

NetSalesUSD = Sales[Units] * Sales[UnitPriceLC] * ( 1 - Sales[DiscountPct] ) / Sales[FXRate]

COGSUSD = Sales[Units] * Sales[UnitCostLC] / Sales[FXRate]

Notice that RELATED walks the chain Sales → Customers → Countries in a single step. That is two nested VLOOKUPs replaced by one function - the payoff for having built the relationships correctly.

And one calculated column in Marketing:

SGAE_USD = Marketing[SGAE_LC] / RELATED( Countries[ExchangeRate_LCperUSD] )

1.7.2 4.2 Core measures

Store the sales measures in Sales and the expense measures in Marketing. Format every one of them as currency with no decimals or as a percentage with one decimal - an unformatted model is an unfinished model.

Net Sales      = SUM( Sales[NetSalesUSD] )
Gross Sales    = SUM( Sales[GrossSalesUSD] )
COGS           = SUM( Sales[COGSUSD] )
Gross Profit   = [Net Sales] - [COGS]
GP Margin      = DIVIDE( [Gross Profit], [Net Sales] )

SG&A           = SUM( Marketing[SGAE_USD] )
EBIT           = [Gross Profit] - [SG&A]
EBIT Margin    = DIVIDE( [EBIT], [Net Sales] )

Discount Rate  = DIVIDE( [Gross Sales] - [Net Sales], [Gross Sales] )
Mkt Intensity  = DIVIDE( [SG&A], [Net Sales] )

Units Sold     = SUM( Sales[Units] )
Orders         = DISTINCTCOUNT( Sales[OrderID] )
Customers      = DISTINCTCOUNT( Sales[CustomerID] )
Avg Sale per Order = DIVIDE( [Net Sales], [Orders] )
Avg Price      = DIVIDE( [Net Sales], [Units Sold] )
Avg Cost       = DIVIDE( [COGS],      [Units Sold] )

Every ratio above is a DIVIDE of two sums - never an average of a ratio column. That is Trap 1 from Part I, enforced by construction.

1.7.3 Validation checkpoint

Put a card visual with Net Sales on a blank page, with no filters. If your consolidated net sales for the two years is not between USD 3.6 and 3.9 million, stop and debug before building anything else.

1.8 Step 5 - Dashboard 1: “Where did our gross profit go?”

Create a report page named 1-PROFITABILITY containing at minimum:

KPI cards: Net Sales, Gross Profit, GP Margin, EBIT, EBIT Margin
A line chart of Net Sales by month with two series (2024 and 2025), so seasonality and growth are visible at once.
A column chart of GP Margin by country, sorted descending, with a constant reference line at the company average.
A matrix: GP Margin by Division (rows) × Year (columns)
A bar chart of the ten products with the lowest GP Margin in 2025, showing their 2024 margin alongside.
Slicers: Year, Region, Division, Channel.
Come up with your own table and chart (In a new Page) that provides you more understanding about the profitability of the company.

Then answer:

Q1. Units sold grew between 2024 and 2025 and net sales grew too - yet gross profit was almost flat. By how much did gross profit actually change, in USD and in percent?

Q2. Consolidated GP Margin fell. By how many percentage points? Is the fall spread across all divisions, or concentrated?

Q3. Two products destroyed a disproportionate amount of margin in 2025. Name them, quantify the margin change, and prove with a visual whether the cause was price, discount, or unit cost.

Q4. Which country has the highest GP Margin, and which has the highest absolute gross profit? Explain why they are different countries and which of the two facts should worry management more.

Q5. Write the insights of your own table/chart. What did you find, and which possible lines of action you would recommend for the future?

1.9 Deliverable

Submit through Canvas:

A link to your published report in the Power BI Service with view access granted to the instructor, and a .pbix export as backup (File → Download this file).
A PDF or Word document containing: screenshots of your Dashboard Pages; and your written answers to Questions Q1-Q5.

1.10 Evaluation rubric

Grading rubric
Criterion	Weight	What earns full credit
Semantic model	10%	Nine relationships with correct cardinality and direction; calendar marked as date table; technical columns hidden; `MonthName` sorted by `MonthNumber`
DAX	40%	Calculated columns vs measures used correctly; every ratio a `DIVIDE` of two sums; the five self-written measures and the three bridge effects correct
Dashboards	20%	Three pages complete, readable, formatted, slicers functional, sorted meaningfully
Insights	20%	Q1-Q5 answered clearly and supported with numbers

Automatic deductions. Any ratio computed as an arithmetic average of a ratio column: -10%. Any figure consolidated without currency conversion: -10%. A report with no formatting - raw decimals, default titles, no thousands separators: -5%.

2 Appendix - DAX quick reference for this workshop

DAX patterns you will need
Task	Pattern
Safe ratio	`DIVIDE( [Numerator], [Denominator] )`
Bring a column from the one side	`RELATED( Dim[Column] )`
Count distinct	`DISTINCTCOUNT( Sales[OrderID] )`
Top N by a measure	`TOPN( 10, VALUES(Dim[Key]), [Measure], DESC )`

3 What comes next

In this workshop you rebuilt in the cloud everything the pre-requisite course taught you on the desktop - a clean fact table, a star schema, correctly weighted ratios, dashboards that answer management questions. From the next workshop we will learn Time Intelligence measures in DAX to calculate growth ratios and other more sophisticated measures.