Zomalex | Data and AI Training

Home | Power BI | Excel | Python | SQL | Gen AI | Public Courses | Prices | Contact Us | LBAG | info@zomalex.co.uk

Financial Sample “Montana” Dataset

This Montana data represents fictional sales transactions by country, product, segment, and date. There are about 700 rows.

Learning Objectives

This dataset can be used in labs to teach:

• pivot tables, VLOOKUP/XLOOKUP functions, aggregation functions and Excel tables in an Excel course
• Understanding Visual Calculations (Power BI course)
• Data Modelling: Building a basic star schema (Power BI Data Modelling course)
• Writing simple DAX columns and measures (Power BI DAX course)

Data

The data is available in a public web location as an Excel file here

This has four tables of data: the main table, Transactions, and three related lookup tables; Product, Country and Segment.

In the Transactions table, we need to create three new columns. These are:

• Gross Sales = Sale Price * Quantity
• Sales = Gross Sales - Discount
• Profit = Sales - COGS

For thePower BI labs, a separate Dates table is available here.

Suggested Approach for Excel lab

Create a pivot table with

• (sum of) Sales and (sum of) Profit on the values area, and
• Region (a column in the Country lookup table) on the rows area, and
• Tier (a column in the Product lookup table) on the columns area

and filter by “Direct” - a value in the Channel column in the Segment lookup table.

Add some data bars to the detail values (not subtotals or totals) of Sales and Profit.

Your pivot table should look like the snapshot below.

Suggested Approach for Power BI labs

Create a [Sales] measure as SUM(Transaction[Sales]).

Create a small multiples column chart to get familiar with the data:

• [Sales] on the y-axis
• Segment on the x-axis
• Country on the legend
• Product on small multiples Create a simple star schema by adding the three dimension tables. Tidy up the fields.

Add the date table to the star schema.

Use Visual Calculations to create calculations:

• running sum
• moving average
• change on previous
• percentage of total
• time intelligence such as year-to-date Create a chart for each of the calculations above.