Visualize Sales, Inventory & Gross Profit with Power BI × Google Sheets (GAS) — A Practical Guide

"Monthly aggregation eats up time." "I can't see gross profit or slow-moving stock right away." Those problems can be solved by combining Power BI with Google Sheets (GAS). This article gently walks through the flow — using a spreadsheet as the data entry point and automatically visualizing sales, inventory, and gross profit — in 8 steps.

*This article contains affiliate links (Amazon Associates).

STEP 01　Decide the goal of visualization

The first thing to decide is "what, by whom, and how often it will be viewed." For example, "the owner reviews sales and gross-profit trends monthly" or "the floor checks slow-moving stock weekly" — make the viewer and purpose clear. Once the goal is set, the data to collect and the charts to build fall into place naturally.

Point　Rather than an elaborate dashboard, deciding on just 1–2 numbers you'll definitely look at daily or monthly lasts longer.

STEP 02　Collect the data (Sheets × GAS)

Consolidate sales, inventory, purchasing, and other data into Google Sheets. Beyond manual entry, GAS (Google Apps Script) can write to the sheet automatically from external systems, email, or APIs. Creating a state where "data accumulates automatically in one place" is the foundation for the automation that follows.

STEP 03　Shape it for analysis (normalization)

A table that's easy for humans to read (with items spreading horizontally) is hard for machines to handle. For analysis, convert to a "long" format with date, product, quantity, and amount as columns. Unify notation inconsistencies (full-width vs half-width, product-name variations) and prepare key columns like a product code, and your data model will connect cleanly later.

STEP 04　Connect to Power BI

The prepared spreadsheet can be connected to and imported directly from Power BI. Load it via a share link or connector, and you can refresh simply by updating the sheet and refreshing Power BI. Set scheduled refresh, and the dashboard switches to new figures automatically.

Point　Getting the import to a state where "one refresh button brings it up to date" is what keeps the operation going.

STEP 05　Design the data model (star schema)

Power BI's real power is decided by how you build the data model. Put "facts" like sales and inventory at the center, and arrange "dimensions" like product, date, and store around them — a star schema — and aggregation gets fast and metrics get easier to build. For example, relate a product master (cost, category) and a date table to your sales-detail fact.

Point　Separate "facts (what you count)" from "dimensions (how you slice)" — that's the foundation.

STEP 06　Build metrics (sales & gross profit with DAX)

Define your numbers with DAX (Power BI's formula language). Beyond sums of sales and quantity, you can compute gross profit and margin against cost, and aggregate automatically by any slice — month, product, category. Define it properly once, and every chart uses the same definition.

Gross Profit =
SUMX (
    FactSales,
    FactSales[Quantity] * ( FactSales[UnitPrice] - RELATED ( DimProduct[Cost] ) )
)

An example (DAX) that computes gross profit per sales line and sums it.

STEP 07　Visualize inventory aging

Inventory costs you money just by sitting there. Split it by days elapsed since receipt into buckets like "0–30 / 31–60 / 61–90 / over 90 days," and slow-moving stock becomes visible at a glance. With DAX's SWITCH(TRUE()) pattern, you can assign buckets dynamically based on days elapsed.

Aging Bucket =
SWITCH (
    TRUE (),
    [DaysElapsed] <= 30, "0-30 days",
    [DaysElapsed] <= 60, "31-60 days",
    [DaysElapsed] <= 90, "61-90 days",
    "Over 90 days"
)

An example (DAX) that builds aging buckets from days elapsed.

Point　Just color-coding slow-moving stock speeds up decisions on markdowns and reordering.

STEP 08　Turn it into a dashboard and operate it

Finally, bring sales trends, gross profit by product, inventory aging, and more into a single-screen dashboard. Share it with stakeholders and set scheduled refresh, and the monthly aggregation work itself becomes unnecessary. What matters in operation is aligning metric definitions to one, and deciding input rules (notation, required columns).

Point　Don't stop at building it — build through to a state where "refresh runs automatically," and it'll keep being used.

---

Wrap-up

Combining Power BI with Sheets (GAS) lets you replace monthly aggregation work with a "dashboard that stays up to date automatically." Rather than trying to build everything at once, we recommend deciding on one number you want to see and building small first.

---

Books to go deeper (PR)

For those who want to try the above themselves — books useful as a first read.

Book (PR)　Power BI primer　For your first visualizations and reports.

Find on Amazon →

Book (PR)　DAX guide　For deeply understanding how to build metrics like sales and gross profit.

Find on Amazon →

Book (PR)　Google Apps Script primer　For automating data collection into spreadsheets.

Find on Amazon →

Book (PR)　Data visualization & dashboard design　For learning how to build dashboards that communicate.

Find on Amazon →

---

Disclaimer: This article is a general technical guide; behavior and screens may differ by version and environment. Affiliate: Robin Planning participates in the Amazon Associates Program and may earn referral fees from purchases via links on this site.