DEV Community: GAURANGSURTE

From Raw Sales to KPIs: DAX, DAX Studio and a Power BI Dashboard on Adventure Works

GAURANGSURTE — Tue, 13 Oct 2026 19:04:42 +0000

The short version
Adventure Works sold $24.91M of goods between January 2020 and June 2022 and kept
$10.46M of it as profit, a margin of 42.0%. Bikes bring in 95% of sales, Australia is the
biggest single region, and December is the strongest month every year. The rest of this post
shows how I built every number with DAX, checked it in a query editor, and put it on a
dashboard.

Aim: To design KPIs and custom DAX calculations on the Adventure Works dataset, validate them
with DAX Studio, and present them in an interactive Power BI dashboard with KPI cards, at
least four visuals and slicers for year, product category and region.

A quick DAX primer
A calculated column is worked out row by row when the data loads and takes up space in
the model. A measure is worked out when a visual asks for it, using whatever filters that
visual applies. Almost every KPI should be a measure.
Two ideas explain most of DAX. Row context means “this one row”, which is what SUMX uses
when it multiplies quantity by price line by line. Filter context means “the rows the visual lets
through”, such as one year or one region. CALCULATE changes the filter context, which is why
it sits behind most of the interesting measures below.
DAX Studio is a separate tool that connects to the open Power BI model. You write a DAX
query, run it, and see the result as a table, which makes it a good place to test a measure
before trusting a card.

The data
The dataset arrives as a set of CSV files that together describe a bicycle company: three
sales files (one per year), lookup tables for products, categories, subcategories, customers
and territories, and a returns table.

The relationships are simple one-to-many links from each lookup table into Sales :
Products[ProductKey] , Territories[SalesTerritoryKey] , Customers[CustomerKey] and
Calendar[Date] to Sales[OrderDate] . Subcategories link to Products and Categories link to
Subcategories. The three sales files are appended into one Sales table in Power Query.

Cleaning checks

The combined sales table has 56,046 rows with no blank cells and no duplicate rows.
Every ProductKey and TerritoryKey in Sales finds a match in its lookup table, so nothing drops out of a relationship.
The customer file is not UTF-8, so I loaded it with the Western European encoding to keep names with accents intact.
The Sales data stops on 30 June 2022, so 2022 is only a half year. I keep that in mind whenever I compare years.
The Components category exists in the lookup table but has no sales, so it never shows up in the charts. The calendar file has only a Date column, so I added four calculated columns to give the
slicers and the monthly axis something to use. The table is marked as the date table, which the time functions need.

Step 1: The ten KPI measures
The data has no sales amount column, so Sales is worked out as quantity times the product price, row by row. RELATED pulls the price from the Products table into each sales row. Cost works the same way, and everything else builds on those two.

Two choices worth stating. Average Sales is the average value of one order line ($444.54),
while Average Order Value is total sales divided by the number of distinct orders ($990.09),
because one order can hold several lines. Average Product Price averages the price list of all 293 products, not the prices of what actually sold.

Step 2: Seventeen more measures

The brief asks for at least twenty measures. These seventeen take the total to twenty-seven.
They cover returns, share of total, filtered totals, ranking and the time-based set used in the next step.

The functions the brief asked for all appear: SUM , AVERAGE , COUNT , DISTINCTCOUNT, CALCULATE, DIVIDE, FILTER and RANKX . I used DIVIDE instead of the / operator
everywhere, since it returns blank instead of an error when the denominator is zero. That matters for the first month of data, which has no previous month.

Step 3: Time-based analysis

Four measures make up the month-over-month view. Monthly Sales is the sales in the month on the axis. Previous Month Sales shifts the date filter back one month with PREVIOUSMONTH .
Sales Variance is the difference, and Sales Growth % is that difference as a share of the previous month.

Monthly sales against the previous month. Sales stayed near $0.5M a month through mid-2021, then climbed to $1.83M in June 2022.

Sales growth % by month. Blue is growth, orange is decline. The same pattern repeats: a big jump in December and a drop in January.

Step 4: Checking everything in DAX Studio

Open the Power BI file, then open DAX Studio and choose PBI / SSDT Model to connect to it.
In the left pane, confirm the eight tables and the measures from steps 1 to 3 are listed.
Write one query that returns all ten KPIs in a single row using EVALUATE ROW(...) .
Run it with F5 and compare each value with the matching card in Power BI.
Run a second query with SUMMARIZECOLUMNS to get monthly sales, previous month, variance and growth, and compare it with the table above.
To look at performance, switch on Server Timings, run the query again, and see how the time splits between the Storage Engine and the Formula Engine.
A measure that needs a lot of Formula Engine time is the first place to simplify.

Screenshot 1: steps performed. The query that returns the ten KPI measures, with the model tables and measures in the left pane and the result grid below.

Step 5: The Power BI dashboard

Layout

Top row: KPI cards for Total Sales, Total Profit, Total Orders, Total Customers and Profit Margin %.
Slicers down the side: Year (from Calendar[Year] ), Product Category and Region.
Trend: line chart with Month Year on the axis and Monthly Sales and Previous Month Sales as values.
Comparison: a column chart of Total Sales by category, a bar chart of Total Sales by region, and a bar chart of the top 10 products by Total Sales (a Top N filter on ProductName ).

How I built it

Load the CSV files from a folder, append the three Sales files, and set column types in Power Query.
Create the relationships in Model view and mark Calendar as the date table.
Add the calculated columns to Calendar, then write the measures from steps 1 to 3 in a dedicated measures table.
Insert one Card visual per KPI and format the numbers: currency for money, whole numbers for counts, two decimals for percentages.
Add the line, column and bar charts, then three slicers. Slicers filter every visual on the page at once.
Keep one colour theme: a single accent for the main metric, grey for comparison series, and the same font throughout.

Screenshot 2: output through cards. The ten KPI measures as cards with no slicer applied. They match the DAX Studio result exactly.

What the numbers say

1. Sales stepped up in mid-2021, and 2022 is on track to be the biggest year
Sales were $6.40M in 2020 and $9.32M in 2021, an increase of 46%. The half year of 2022 already reached $9.19M, almost the whole of 2021. Comparing the same six months, January to June 2022 is 211% higher than January to June 2021 ($9.19M against $2.95M).

Sales and profit by year. 2022 covers January to June only.

2. Bikes are the revenue, accessories are the margin

Bikes produce 94.9% of sales ($23.64M) at a 41.1% margin. Accessories are 69% of all units sold but only 3.6% of sales, and they earn the highest margin at 62.8%. Clothing is the smallest category at $0.37M. Accessories and clothing first appear in the data in July 2021, so they only explain a small part of the growth since then. Most of it comes from bikes: bike sales in the second half of 2021 were nearly double those of the first half.

Sales and profit margin by category.

3. Top-selling products

All six Mountain-200 variants sit at the top of the revenue list and together bring in $7.17M.
The Mountain-200 Black, 46 is the number one product at $1.24M. By units it is a different list: the 30 oz water bottle leads with 7,967 units, followed by the patch kit and the mountain tyre tube. Cheap accessories give volume, expensive bikes give revenue.

Top 10 products by sales

4. Australia leads, and two US regions carry the United States

Australia is the largest region with $7.42M, which is 29.8% of sales. The Southwest ($4.82M) and Northwest ($3.10M) are next, and together they make up almost all US sales. The Central, Northeast and Southeast US regions are almost empty, each under $12K. Canada, France and Germany are mid-sized, and the United Kingdom is the strongest European region.

Sales by region

5. Seasonality, order value and returns

December is the peak: sales rose 73% over November in 2020 and 44% in 2021. January then fell 23% and 22%.
Average order value fell **from $2,435 in 2020 to $872 in 2021 and $776 in 2022. Orders more than quadrupled once accessories and clothing arrived, and those orders are small.
**Profitability is steady: the margin was 40.6% in 2020, 42.5% in 2021 and 42.3% in 2022.
Returns are low: 1,828 units came back, which is 2.17% of units sold.

What I would do next

Stock up and promote ahead of December, and plan for the January dip instead of being surprised by it.
Push accessories alongside bike orders. Their margin is 22 points higher than bikes and they already sell in volume.
Look into why the Central, Northeast and Southeast US regions sell almost nothing, since the product clearly sells in the Southwest and Northwest.
Review jersey pricing or cost, as their margin is well below everything else.

Conclusion

Writing the measures taught me that most of the work in a KPI dashboard is deciding what each number means. Sales had to be built from quantity and price, an order and an order line are not the same thing, and a growth percentage on a half year is easy to misread.
Checking the same values in DAX Studio and on the cards made me trust them. The result is a set of 27 measures, a dashboard that answers the six questions in the brief, and a clear picture of a business that grows fast, depends on bikes, and peaks every December.

Power Query Deep Dive — Transforming & Analyzing the AdventureWorks

GAURANGSURTE — Fri, 21 Aug 2026 06:44:50 +0000

Power Query Deep Dive: Transforming & Analyzing the AdventureWorks Sales Dataset in Power BI

If you've ever wondered how a pile of raw CSV exports turns into a clean, business-ready Power BI dashboard, this post walks through exactly that — using Power Query's four core transformation techniques (Grouping, Pivoting/Unpivoting, Appending, and Merging) on the classic AdventureWorks retail dataset, then layering on DAX measures and a full sales-analysis report.

The dataset

AdventureWorks is a sample dataset for a fictional bicycle manufacturer: Internet Sales order lines joined to Product, Subcategory, Category, Customer, and Territory lookup tables — a textbook star schema with one fact table and several dimension tables.

Step 1 — Grouping

Grouping collapses transaction-level rows into summarized metrics. A few of the groupings I built:

Category → Sum of Sales Amount — total sales per product category
Customer → Average of Sales Amount — average spend per customer, plus a Top N filter for the top 10 customers by total sales
Subcategory → Sum of Order Quantity — units sold per subcategory
Territory → Average of Order Quantity — average basket size by region
Customer → Count Distinct of Order Number — orders placed per customer
Category → Max of Sales Amount — highest single sale per category
Territory → Sum of Tax Amount — total tax collected per region

Step 2 — Pivoting & Unpivoting

To prove the transformation is lossless, I built a small reference table with Category, Sales Amount, Tax Amount, and Order Quantity, then:

Unpivoted the three numeric columns into a tall Attribute / Value layout
Renamed them to Metric / Amount
Pivoted the Metric column back on Values = Amount

The row and column counts matched the original table exactly after the round trip — confirming nothing was lost in translation.

Step 3 — Appending

I split Internet Sales into two queries by Order Date/Year (First Half and Second Half) and combined them with Append Queries → Append as New. The resulting row count matched the sum of both halves exactly, with no duplicates or schema mismatches.

Step 4 — Merging

This is where the star schema comes together:

Merge FactInternetSales with DimProduct on ProductKey (Left Outer Join) → expand Product Name, Color, Standard Cost
Merge the result with DimProductSubcategory → expand Subcategory Name
Merge again with DimProductCategory → expand Category Name

After Close & Apply, Manage Relationships confirmed every fact-to-dimension link was Active with the correct cardinality.

DAX measures

With a clean star schema in place, six measures power the whole report:

Total Sales =
SUMX(
    'Sales Data',
    RELATED('AdventureWorks Product Lookup'[ProductPrice]) *
    'Sales Data'[OrderQuantity]
)

Total Cost =
SUMX(
    'Sales Data',
    RELATED('AdventureWorks Product Lookup'[ProductCost]) *
    'Sales Data'[OrderQuantity]
)

Total Profit = [Total Sales] - [Total Cost]

Total Orders = DISTINCTCOUNT('Sales Data'[OrderNumber])

Total Customers = DISTINCTCOUNT('Sales Data'[CustomerKey])

Total Quantity = SUM('Sales Data'[OrderQuantity])

The results

Once the model was built, the numbers told a clear story:

Metric	Value
Total Sales	24.91M
Total Cost	14.46M
Total Profit	10.46M
Total Orders	25,164
Total Customers	17K
Total Quantity Sold	84K

A few standout findings from the sales analysis:

Bikes dominate — the category alone drives roughly 95% of total revenue.
Territory 9 leads by a mile — 29.8% of total sales, about 54% ahead of the second-place territory.
Mountain-200 Black, 46 is the single highest-selling product/size, at ~1.24M in sales.
Sales peak in June (~3.0M) then fall off a cliff in July (~1.15M) before slowly recovering into December — a pattern that repeats across all three years in the data.
Weekday vs weekend basket size is nearly identical (~446 vs ~441 average sale), so weekday revenue is simply higher because there are more weekdays, not because people spend more on them.

Advanced tasks

On top of the core analysis, I added a few DAX/Power-Query extras:

Ranking product categories by total sales
Above-average product detection — 39 of 130 products (~30%) sit above the average of ~191,650 in cumulative sales
Conditional-Column customer bands — Low / Medium / High spending tiers based on the 25th/75th percentile of customer spend (17,343 customers)
Conditional-Column sales bands — Low / Medium / High per order line based on Sales Amount

Wrapping up

Grouping, Pivoting/Unpivoting, Appending, and Merging cover almost everything you need to turn messy multi-table exports into a trustworthy star schema — and once that's in place, a handful of DAX measures is enough to power a complete sales dashboard. If you're learning Power BI, working through all four transformations on a real dataset like AdventureWorks (rather than just one) is the fastest way to actually understand why each one exists.

The Territory

The Mountain-200 family dominates the top of the ranking, led by Mountain-200 Black, 46 at roughly 1.24M in total sales the single highest-selling product/size combination in the catalogue. Road-250 and Road-150 variants fill most of the remaining top-20 slots, with Touring-1000 models appearing further down.

The Calendar Sales

Aggregated across the full 2020–2022 date range, monthly sales climb steadily from January (~2.3M) through a June peak (~3.0M), then drop sharply in July (~1.15M) and stay low through Q3 before recovering toward year-end, closing December at roughly 2.2M. This first-half-heavy, third-quarter-dip pattern is consistent across the three years in the underlying data.

*Full write-up with all steps and screenshots: see my https://1drv.ms/w/c/70201b25f03037d4/IQCfxH5eIuXtSKltpP5bKTA2AaY4gLJKsGtUgg4PeX8eFg8?e=HTC7T2 *

24 Years of Climate Data, Cleaned and Built a Dashboard and Here is the analysis of What I Found

GAURANGSURTE — Wed, 15 Jul 2026 20:07:59 +0000

Climate change is no longer a prediction;
it's something we experience every year. Rising temperatures, increasing sea levels, extreme weather events, and changing forest cover are all interconnected. While reports and datasets contain enormous amounts of information, understanding the relationship between these indicators becomes much easier through interactive visualizations.

For this project, I built a Climate Change Dashboard in Power BI that combines multiple environmental indicators into a single analytical view. Instead of focusing on one metric, the dashboard brings together temperature trends, carbon emissions, renewable energy adoption, sea level rise, forest coverage, population, and extreme weather events to help identify patterns across countries and over time.

Why I Built This Dashboard

Are countries investing more in renewable energy?
Does higher renewable energy usage always correspond to lower carbon emissions?
How has average temperature changed over time?
Which countries experience more extreme weather events?
Is forest cover changing alongside emissions?

The primary challenge: involved selecting visualizations capable of representing multiple environmental indicators without overwhelming users.

Preventing information overload.
Ensuring readability across multiple countries.
Balancing analytical depth with dashboard simplicity.
Data visualization addresses this challenge by presenting multiple variables simultaneously in a structured and interactive format. Rather than displaying numerical values in tables, dashboards enable users to identify trends, compare countries, explore geographical distributions, and investigate the interaction between different environmental factors.

I built a simple dashboard that contains the all required information and relevance with the components of PowerBI with visualization as the image are the simplest but technical things that humans are mastered to figure and identify patterns from them the current tools such as PowerBI does the same with the low efforts and efficient way

Key Performance Indicators (KPI Cards)

KPI cards provide an overview of the most important environmental metrics within the dataset.

The dashboard displays:

Average Temperature
Sea Level Rise

These metrics function as summary indicators, allowing users to understand the current state of environmental conditions before exploring detailed visualizations.

Scatter Plot Analysis

The scatter chart represents one of the most informative visualizations within the dashboard.

It establishes a relationship between:

Renewable Energy Percentage (X-axis)
CO₂ Emissions per Capita (Y-axis)
Population (Bubble Size)
Year (Animation)

This visualization enables multidimensional analysis by simultaneously displaying four variables.

Line Chart for Temperature Trends
Temperature values are visualized using a line chart to represent changes across multiple years.

Each line corresponds to a country, enabling direct comparison of warming patterns over time.

Filled Map for CO₂ Emissions

Geographical visualization is achieved through a filled map representing CO₂ emissions across countries, the map introduces spatial context that cannot be captured using conventional charts.

Forest Area Comparison

Forest coverage is displayed using a horizontal bar chart, countries are ranked according to their forest area percentage, allowing users to compare environmental resources efficiently.

Decomposition Tree

The decomposition tree is used to analyze Extreme Weather Events through hierarchical exploration.

Users can progressively drill down by:

Country
Year

This transforms static reports into an interactive analytical experience.

Analytical Interpretation: Trend Analysis, Correlation Analysis, Comparative Analysis, Spatial Analysis, Hierarchical Analysis

The Climate Change Dashboard illustrates how Business Intelligence tools can be applied to environmental datasets to support data-driven analysis. By integrating multiple climate indicators into a single interactive platform, the dashboard enables users to explore relationships, compare countries, investigate trends, and gain a comprehensive understanding of global environmental changes.

Rather than functioning as a static reporting system, the dashboard promotes exploratory analysis through interactive filtering, hierarchical drill-down, and multidimensional visualization. This approach not only improves accessibility but also enhances the ability to derive actionable insights from complex climate data.

Ultimately, the project demonstrates that effective dashboard design is not solely about creating visually appealing charts but about selecting appropriate visualizations that communicate information clearly, support analytical thinking, and facilitate informed decision-making.