DEV Community: GAURANGSURTE

Self-Healing Data: How Two-Dimensional Parity Lets a Network Fix Its Own Mistakes

GAURANGSURTE — Mon, 19 Oct 2026 19:46:25 +0000

The parity trick that answers “exactly where?” and not just “something is wrong”

Why should you care?

Every frame crossing a cable or the air faces noise from interference, loose connectors or even a microwave oven. One flipped bit can turn a transfer of 100 into 900. The link layer’s first duty is to notice damage, and the receiver usually discards the frame and asks for a resend. On satellite or noisy wireless links, resending is slow and may fail again, so it would be better if the receiver could repair the damage itself. Two-dimensional parity shows that idea in its simplest form.

Step 0: the one-bit recap

In simple parity, the sender appends one extra bit so that the total number of 1s is even. If the receiver counts an odd number, it knows a bit has flipped. The scheme has two weaknesses that our course slides point out: two flipped bits cancel each other and slip through unnoticed, and even when a flip is caught, the receiver has no idea which bit went bad. Detection without location means no repair.

A quick illustration: the data 101010 has three 1s, so the sender appends a 1 and transmits 1010101. If noise flips two bits and the receiver sees 1111101, it counts six 1s, an even number, and wrongly accepts the frame.

The big idea: arrange the bits in a grid

Instead of guarding one long row of bits, write the data as a small table. Then protect it twice, once along every row and once along every column. It works like naming a cell in a spreadsheet: a row number and a column number together point to exactly one cell, so two failing checks reveal the damaged bit.

Why does this work? A single flipped bit changes the count in exactly one row and exactly one column, and in no other. Every bit sits at a unique row-and-column crossing, so the pair of failing checks narrows the error down to a single bit.

Sender: arrange the data in rows, add an even-parity bit at the end of each row, add an even-parity bit under each column, and send the whole block.
Receiver: recount every row and every column of the block that arrived.
Decision: if every count is even, accept. If exactly one row and one column are odd, the bit where they cross is the culprit, so flip it back.

Figure 1. Row and column parity on a 4×4 block; the failing row 3 and column 2 expose the flipped bit.

Worked example, step by step

Take the four data rows 1011, 0110, 1100, 0001. Their row-parity bits are 1, 0, 0, 1(row one has three 1s, so we add a 1 to make four; row four has one 1, so we add another). The column-parity bits all come out as 0, and so does the corner bit. Now suppose noise flips the second bit of the third row from 1 to 0. At the receiver, row 3 now holds a single 1, which is odd, and column 2 also holds a single 1, which is odd. Every other row and column still has an even count. The receiver points at row 3, column 2, flips that bit back to 1, and the original block is restored without asking for a retransmission.

Notice what the receiver never needed: a second copy of the data. It only checked the rule “every row and every column must hold an even number of 1s”. That makes correction cheap to compute, just a few additions and one bit flip, which is why the idea is so easy to build into hardware.

What it can and cannot do

Error pattern	Outcome
1 bit flipped	Detected and corrected (row + column cross at the bad bit)
2 or 3 bits flipped	Detected, but not correctable. Two errors at (1,1) and (2,2) leave the same failing rows and columns as two errors at (1,2) and (2,1), so the receiver cannot tell which pair is real
4 bits at the corners of a rectangle	Slips through. Every affected row and column sees an even number of changes, so all parities still look fine
Burst of consecutive bits in one row	Usually detected (each column sees one change) but not repairable

There is also a price. A grid with r rows and c columns needs r + c + 1 extra bits. For our 4×4 block that is 9 extra bits on top of 16, which is heavy. For an 8×8 block it is 17 on top of 64, about 27 percent. This overhead is why modern links such as Ethernet rely on a CRC to detect trouble and on retransmission to repair it, and keep heavier correcting codes for places where resending is painful.

Burst errors deserve a mention. Real noise often damages several neighboring bits at once. If a burst lands inside one row, the row parity may miss it (an even number of flips cancels), but each affected column sees one change, so the damage is still detected. Correction is impossible, however, which is why long bursts are the natural job of CRC.

Real-world case study: rebuilding a dead disk

The same “parity lets you recover missing data” principle runs inside the storage servers behind everyday cloud services. In RAID 5 (and its simpler cousin RAID 4), the system stores data blocks on several disks plus a parity block, calculated using XOR, which is just even parity applied bit by bit. If one disk dies, the controller already knows which disk is missing, so it XORs the surviving blocks to reconstruct the lost one. Figure 2 shows the idea with two data disks. Real RAID 5 rotates the parity block across all disks so that no single disk becomes a bottleneck.

Figure 2. Parity-based recovery, simplified.

One subtle point: this rebuild works because the failed disk is known. Recovering a known missing piece is easier than finding an unknown flipped bit, which is what the crossing in Figure 1 solves.

Two more places where the idea reappears: ECC memory in servers uses Hamming-style codes that locate and fix a flipped bit, much like our row-and-column crossing, and wireless or satellite systems use forward error correction so that receivers can repair small damage without waiting for a retry.

Try it yourself

Flip the **last data bit of row 1** in the example. Which row and column fail? Answer: row 1 and column 4.

Key takeaways

Simple parity detects errors but cannot locate them.
Two-dimensional parity corrects one flipped bit, detects up to three, and misses a four-bit rectangle.
Correction costs extra bits, so real links combine CRC, retransmission and error-correcting codes.
It also protects RAID storage and ECC memory.

References and further reading

Course slides, Unit 5: The Link Layer, Computer Networks, SIT Pune. https://1drv.ms/p/c/70201b25f03037d4/IQA-NAtD0wsvTL8TOua3pPOdAaMoqMv5jX14hMvdmPymtTY?e=juVfWO
Kurose, J. and Ross, K., Computer Networking: A Top-Down Approach, Pearson (link-layer chapter).

gaia.cs.umass.edu
ijnrd.org

Adding R to Power BI: Statistics, Trends and a Six-Month Sales Forecastfor Adventure Works

GAURANGSURTE — Thu, 15 Oct 2026 05:34:59 +0000

Aim

To integrate R with Power BI, use R for statistical analysis, advanced visualization and timeseries forecasting, and combine the results with Power BI KPI cards, slicers, filters and drilldown in one professional, interactive dashboard.

Objectives
Set up R with Power BI and enable R script visuals.
Import and clean the Adventure Works data and calculate summary statistics.
Create at least four R visuals, five KPI cards and four native Power BI visuals.
Run an advanced analysis (a time-series forecast) with an R package.
Add slicers, filters and a drill-down, then write the business insights.

Tools: Power BI Desktop, R (with RStudio for testing), packages ggplot2 , forecast and scales.
Dataset: Adventure Works sales, product, territory and customer files.

The short version

Power BI is excellent at totals and slicers but cannot draw a box plot or fit a forecasting model on its own. R can. I connected R to Power BI and built a dashboard that answers one business question: where is Adventure Works sales heading? The history is $24.91M of sales and $10.46M of profit (42.0% margin) from January 2020 to June 2022. Monthly sales climbed from about $0.5M to $1.83M, and an ARIMA model projects them to keep rising over the next six months, with a wide band of uncertainty around that path.

Why bring R into Power BI?

Power BI’s built-in visuals cover the everyday cases: totals, trends, rankings, shares. They do not show the distribution of a value, test a statistical idea or predict the future. R fills that gap with thousands of packages, and Power BI can run an R script and show the resulting picture as a normal visual on the page.

An R visual works like this. You drop fields into the visual, Power BI hands those columns to your script as a data frame called dataset , the script draws a plot, and Power BI displays the plot. Because the visual is part of the report, slicers and filters still change it. Two details matter. First, duplicate rows are removed from dataset unless the fields include something unique such as OrderNumber and OrderLineItem . Second, an R visual re-runs its script on every filter change, so it is slower than a native visual and the output is a static image, not a clickable chart.

The model I used for forecasting is ARIMA (AutoRegressive Integrated Moving Average). It predicts the next value of a series from its own past values and past errors. auto.arima() from the forecast package tries many combinations and keeps the one with the best fit, and forecast() then projects it forward and adds prediction intervals.

Task 1: Setting up R with Power BI

Install R from CRAN and, optionally, RStudio. Install the packages once from the R console: install.packages(c("ggplot2","forecast","scales"))
In Power BI Desktop open File > Options and settings > Options > R scripting. Check that the R home directory points to the R installation, and set RStudio as the external editor if wanted.
Switch on Enable script visuals when Power BI asks, and add the R visual to the page from the Visualizations pane (the R icon).

Task 2 and 3: Importing and cleaning the data

The data comes as CSV files. In Power Query I appended the three yearly sales files, joined them with the product, subcategory, category, territory and customer tables, and kept one
flat table with one row per order line (56,046 rows). Sales is not stored in the files, so I calculated it as OrderQuantity × ProductPrice , and cost and profit in the same way.

The data stops on 30 June 2022, so all analysis uses January 2020 to June 2022. The final query in Power Query looks like this.

Task 5: Summary statistics

Before drawing anything I looked at the numbers behind the sales column. In R the one-liner is summary(dataset$Sales) ; the results for the 56,046 order lines are below.

The mean is more than twelve times the median and the standard deviation is almost twice the mean. That tells me the data is heavily right-skewed: most order lines are small accessories, and a minority are expensive bikes that pull the average up. It also explains why a log scale is needed on the plots below.

Task 4 and 6: The R visuals

Every R visual below uses fields from the same flat table, and each one is a short ggplot2 script.

R visual 1: Histogram of order-line value

Fields used: Sales . Power BI shows the warning “Duplicate rows will be removed from the data” above the script, which is why unique keys such as OrderNumber and OrderLineItem are worth adding when every order line must count.

The histogram and its script in Power BI’s R script editor. Two separate clusters appear: a large group of small accessory and clothing lines around $10 to $60, and a smaller group of bike lines between about $500 and $3,500.

R visual 2: Box plot of sales by category

Fields used: CategoryName , Sales.

Sales per order line by category. Bikes sit one to two orders of magnitude above accessories and clothing, and the three boxes barely overlap.

R visual 3: Monthly sales with a smoothed trend

Fields used: Month , Sales . The script adds up sales per month and fits a LOESS smoother, shown as the dashed line.

Monthly sales (red) and the LOESS trend (dashed). Sales slid from about $0.6M in early 2020 to a low near $0.33M in November 2020, stayed flat through mid-2021, and then accelerated to $1.83M in June 2022

R visual 4: Six-month ARIMA forecast

Fields used: Month , Sales . This is the advanced analytics task, covered in detail next.

Task 7: Advanced analytics, a time-series forecast

The forecast script does five things.

Adds up sales per month and sorts them by date.
Builds a monthly time series with ts() , using frequency = 12 and a start year and month taken from the data.
Lets auto.arima() pick the model orders.
Projects six months ahead with forecast(..., h = 6).
Draws the history and the forecast with autoplot().

Screenshot 1: steps performed. The forecast visual with its R script in the script editor. The blue line is
the point forecast, the dark band is the 80% interval and the light band the 95% interval.

How to read the forecast

Direction: the forecast starts at roughly $1.9M for July 2022 and rises to roughly $2.4M by December 2022, a continuation of the upward trend.
Uncertainty: the band fans out with every month. By December the 95% range runs from about $1.6M to $3.2M. The model is confident about next month and honest about how little it knows about month six.
Limits: the series is only 30 months long, which is short for a seasonal model, and December 2021 was a visible spike of $1.64M. With so little history the model cannot separate a true December effect from a one-off, so the forecast mostly follows the recent trend. It should be read as a planning range, not a promise.

Task 8: Building the interactive dashboard

Layout

Title banner across the top: “Adventure Works: Sales Analytics with R and Power BI”.
Five KPI cards: Total Sales, Total Profit, Total Orders, Total Customers and Profit Margin %.
Three slicers: Region, Year and CategoryName. They filter every visual on the page, R visuals included.
Four native visuals: a line chart of sales by year and month, a column chart of sales by category with drill-down, a donut chart of profit by category and a bar chart of sales by country.
Four R visuals: the histogram, the box plot, the monthly trend and the forecast, on pages 3 and 4.

The pages I built

The KPI measures

Screenshot 2: output through cards. Five KPI cards (25K orders, 41.97 profit margin %, 17K customers, $10.46M profit, $24.91M sales) with the Region, Year and CategoryName slicers.

The Power BI visuals: sales by year and month with drill-down, sales by category, profit by category and sales by country.

Filters and drill-down

The Sales by Year / Month line chart uses a date hierarchy, so the drill arrows move from the yearly view to the monthly one.
The Sales by Category (drill down) column chart is built on a hierarchy that starts at category, so it can be drilled into finer levels.
The Region, Year and CategoryName slicers filter the visuals on the page. Choosing “Bikes” or “2021”, for example, redraws the charts and the R visuals together.

R visuals page, part one: histogram and box plot as report visuals.

R visuals page, part two: monthly trend and the six-month forecast.

Task 9: Business insights

Bikes carry the business

Bikes are 94.9% of sales ($23.64M) and 93% of profit ($9.73M). Accessories add only $0.57M of profit (5.45%) and clothing $0.16M (1.55%), as the donut chart shows. A small change in bike demand moves the whole company.

Growth is real, and it began in mid-2021

Yearly sales were $6.40M in 2020 and $9.32M in 2021, and the first half of 2022 alone reached $9.19M. The trend line shows the change clearly: flat at about $0.5M a month until June 2021, then a steady climb. Accessories and clothing started selling in July 2021, which
coincides with the jump.

Order values are split into two worlds

The histogram and box plot show two kinds of sale: many small lines of $10 to $60 and fewer large bike lines of $500 to $3,500. Averages hide this. The mean line value is $444.54, yet the median is only $34.99. Decisions about average prices or discounts should always be made per category.

Geography is concentrated

The United States leads with $7.94M and Australia is the biggest single region at $7.42M. Canada is the smallest of the six country markets at about $1.8M, and Germany, France and the United Kingdom each sit between $2.4M and $2.9M.

The forecast says “keep growing”, with caution

The model expects monthly sales to rise from about $1.9M to $2.4M by December 2022. The widening band means inventory and staffing plans should be set against the lower and middle parts of the range and reviewed monthly as actual figures arrive.

What I would do next

Plan stock around the December peak and the January dip seen in both years.
Cross-sell accessories with bike orders, since they are cheap and high margin.
Re-run the forecast every month and compare it with actuals to see whether the model needs a seasonal term once more history exists.

Task 10: Conclusion

This practical showed me what each tool is best at. Power BI handled the KPIs, slicers and drill-down well, and R handled the distribution plots and the forecast that Power BI cannot do natively. Getting the two to work together took more care than I expected: the R path had to match the installed libraries, duplicate rows are silently removed unless unique keys are included, and R visuals refresh more slowly than native ones. The final dashboard combines five KPI cards, four native visuals and four R visuals, shows a business that grows fast and depends on bikes, and gives a six-month outlook with an honest measure of uncertainty.

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.