Data Training Courses

Home | Power BI | Excel | Python | SQL | Generative AI | Visualising Data | Analysing Data

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:

• 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 Excel tables: Transaction, a fact table, and three related dimension tables; Product, Country and Segment.

A separate Dates table is available here.

Suggested Approach for Power BI labs

There are three useful variables that we can calculate from values in other columms of the same row, so we can implement either as a new column in the Query Editor or as a DAX calculated column. The formulas are:

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

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.