Square to Power BI, done for you
Your Square Power BI dashboard starts with the extraction decision, not the first visual: the Orders API, the Payments API, the Reporting API or Dashboard exports, each under read-only scopes on an app you own. Then gross sales, net sales, total collected and net total get unstacked into measures that survive an audit. Built for multi-location retail, counter-service food, salons and spas, and mobile services. Most builds ship in 3–5 business days.
Representative dashboard — sample data
Where Square reporting runs out
Square’s Sales Summary is not wrong — it is stacked. Net sales, gross sales, total collected and net total sit on the same page as four different answers to “what did we make?”, and each one folds in a different combination of tax, returns, discounts, comps, tips, service charges and processing fees. Export it to Excel and the stack travels with you. Unstacking it into measures that each mean one thing, and that reconcile back to a Square report your bookkeeper already trusts, is the first real piece of work on any build.
The second is grain. Square holds a sale in at least four shapes at once — the order and its line items, the payment and its tenders, the catalog object that was actually sold, and the payout entry that eventually hits the bank — and each shape answers a different question. Refunds and disputes are their own grains again, with their own timing. A model that flattens all of that into one table produces a dashboard that looks decisive and quietly double-counts.
Two Square-specific caveats we raise before anyone approves a scope, because they change what the report can honestly claim: cash tips are not in Square’s tip figures — Square’s own definition excludes them because they are never recorded in the point-of-sale app — and processing fees cannot be attributed to an item category, because Square charges the fee on the payment total rather than on the items inside it. Both are solvable. Neither is solvable by pretending.
Reports we build from Square
Buy a block of prepaid Flex hours and, if you’re unhappy within the first 4 hours, we refund you in full — no questions asked. Hours are deducted only for work you’ve reviewed and approved, there’s no project minimum, and your banked hours never expire.
What the build covers
Every one of these is settled in the scope estimate, in writing, before an hour is spent.
Orders, Payments and Reporting answer different questions and carry different grain. We pick the route per report, register the OAuth application in your account, and ask only for read scopes — ORDERS_READ, PAYMENTS_READ, PAYOUTS_READ, ITEMS_READ, MERCHANT_PROFILE_READ — so nothing we build can write to your Square account.
Gross sales, net sales, total collected and net total become four explicit measures rather than four labels, with tax, returns, discounts, comps, service charges, tips and fees each carried on their own line. Every one ties back to a Square report your team already reconciles.
Square excludes cash tips from its tip totals because the point-of-sale app never sees them. We label the measure card-only on the report face, and where you keep a declaration sheet or a tip-out log we blend it in as a named second source rather than quietly inflating the number.
Square prices the fee on the payment total, so no Square report links fees to an item category — one sale spanning three categories has one fee and no category. We model fees at payment grain and, if you want a category view, allocate on a basis you approve and the report names out loud.
Square’s payout entries carry gross, fee and net amounts for each charge, refund, fee and adjustment inside a batch. We model them so the number on the bank statement opens into a drillable list instead of becoming a month-end spreadsheet hunt.
The Locations API gives each site its own identifier, currency and timezone — which is what makes a day boundary correct across a chain. The catalog is modelled at item-variation grain, because a size or colour is what actually sold, and renamed items still reconcile to their history.
Through an extraction you authorise, because Power BI Desktop ships no first-party Square connector and there is no supported way to point it at Square’s database. The realistic routes are Square’s published APIs under an OAuth application registered in your own Square account, or scheduled report exports from the Seller Dashboard. We register the application against your account, request read-only scopes, and hand you the credentials — you can revoke our access at any time without touching the model.
The choice between APIs is not a preference, it is a question of what the report needs. Orders and Payments give raw, joinable detail at the grain you model from. The Reporting API — Square’s newer, Cube-based analytics endpoint, published as a beta at the time of writing — returns pre-aggregated measures such as Orders.net_sales and Orders.tips_amount from a handful of universal endpoints, which is fast to stand up but refreshes on its own cadence and is a poorer fit where you need row-level drill-through. Most builds use more than one.
| Route | Read-only OAuth scope | What it gives you | When we use it |
|---|---|---|---|
| Orders API | ORDERS_READ |
Orders searchable by location and closed-at window, with line items, quantities, taxes, discounts, service charges, returns and the catalog object behind each line | The default spine of the model — the only route that carries the line-item grain item mix, category margin and modifier analysis actually need |
| Payments API | PAYMENTS_READ |
Tender-level detail: amount, tip, total, card brand and entry method, cash vs card vs wallet source, and the processing fee entries attached to each payment | Whenever fees, tender mix, tipping behaviour or split payments are in scope — none of which the Orders API answers |
| Reporting API (Cube-based) | REPORTING_READ |
Pre-aggregated measures and dimensions across Orders, PaymentAndRefunds and ItemTransactions cubes, queried through schema-discovery and load endpoints rather than fixed routes | Executive summary pages and fast first drafts, where an aggregate is the deliverable and row-level drill-through is not |
| Payouts API | PAYOUTS_READ |
Payout batches and their entries, each with gross, fee and net amounts by entry type — charge, refund, fee, adjustment and dispute activity | Any build where finance has to tie the Square balance to the bank balance |
| Catalog & Locations APIs | ITEMS_READ, MERCHANT_PROFILE_READ |
Items, item variations, categories, modifiers and taxes; plus each location’s identifier, name, currency, status and timezone | Always — these are the dimensions. Without them the fact tables are lists of identifiers |
| Seller Dashboard report exports | None (operator-run export) | Saved Sales Summary, item sales and transactions output at whatever grain the report itself carries | Where an OAuth application cannot be approved in time, or a figure your team already reconciles exists only inside an established Square report |
| An existing warehouse copy | Depends on the store | Square data already landed into Azure SQL, Microsoft Fabric, Snowflake or a partner platform by someone else | Always our first question — faster to refresh, cheaper to model, and already joinable to accounting |
One caveat on scopes worth knowing before you ask your developer: REPORTING_READ is documented with the Reporting API itself rather than alongside the long-established permissions, so it is worth confirming availability on your account before a report is designed around it. We check that during scoping, not after.
Square publishes precise definitions for its reporting terms, and they nest. Net sales are gross sales less returns, discounts and comps. Total sales are net sales plus taxes, tips and gift card sales, less additional-amount refunds. Total collected is payments less refunds. Net total is total collected less fees and other withholdings. Read quickly, those look like synonyms. Modelled quickly, they produce a dashboard that disagrees with the ledger by a predictable amount nobody can find.
So we build each layer as its own measure with its own definition written down, then reconcile the whole set against a Square report your bookkeeper already runs before anything is published. That reconciliation is the deliverable that makes the rest trustworthy.
| Square term | What Square includes in it | How the model carries it |
|---|---|---|
| Gross sales | Item sales before returns, discounts and comps are taken off | A base measure at line-item grain, never used alone as “revenue” |
| Net sales | Gross sales less returns, less discounts and comps | The default revenue measure, with returns, discounts and comps each broken out as their own measure so the bridge is visible |
| Taxes | Tax amounts applied at the time of sale | A separate measure, excluded from revenue and reported for filing |
| Tips | Voluntary gratuity added to the transaction — excluding cash tips, which the point-of-sale app does not record | Labelled card tips on the report face; cash tips only appear if you supply a declaration source we name in the lineage |
| Service charges | Auto-gratuity, surcharges and similar charges added to the sale | Kept apart from both tips and item revenue, because they behave differently for labour and for tax |
| Total collected | Payment amount less refund amount | A payment-grain measure, reconciled against net sales plus tax plus tips rather than assumed equal to it |
| Fees | Processing and service fees taken at the time of payment, excluding taxes | Payment grain only — never attributed to a category without a stated allocation basis |
| Net total | Total collected less fees and other withholdings | The measure that should approach the bank deposit, and the one payout reconciliation is tested against |
Because Square records one sale in several shapes simultaneously, and a dashboard that ignores that will double-count within a week of going live. One order can carry several payments when a customer splits a tender. One payment can settle in a payout days later. A refund is not a negative order, and a dispute is not a refund — it has its own lifecycle, its own timing and its own fee. Each of those is a fact table with its own key, related through shared dimensions rather than stapled together.
| Grain | Source | Key | What it answers |
|---|---|---|---|
| Order line item | Orders API | Order ID + line item UID | Item and variation mix, category and modifier margin, discount and comp incidence, basket composition |
| Order | Orders API | Order ID | Average ticket, order counts, fulfilment and channel mix, day and daypart shape |
| Payment | Payments API | Payment ID | Tender mix, card brand and entry method, card tips, processing fee cost, split payments |
| Refund | Refunds data on payments | Refund ID | Refund rate and reason, refunded fee treatment, which staff member and location issued it |
| Dispute | Disputes API (DISPUTES_READ) |
Dispute ID | Chargeback rate, dispute state and outcome, exposure by location and card brand — a different question from refunds |
| Payout entry | Payouts API | Payout ID + entry ID | Bank reconciliation: gross, fee and net per charge, refund, fee and adjustment inside each deposit |
| Item variation | Catalog API | Catalog object ID | The dimension the line items point at — size, colour, SKU, price point, and the item and category above it |
| Location | Locations API | Location ID | Site, region and concept roll-ups, currency, and the timezone that makes a business day correct per site |
Square documents the Payouts API as the way to reconcile a seller’s Square balance against their bank balance, and that is exactly the job. Each payout is a batch; each entry inside it carries a type — charge, refund, fee, adjustment and dispute activity — alongside gross, fee and net amounts. Model the entries and the deposit on the bank statement becomes a clickable list rather than a figure someone reverse-engineers at month end.
Two timing effects are worth designing for rather than discovering. Processing fees attach to a payment after capture and can be adjusted afterwards, so a fee total pulled on the day of sale is not always the fee total that settles. And a payout lands days after the sales inside it, which means payout-period reporting and sales-period reporting will never line up on the same calendar — they are answering different questions, and the model should say so on the page rather than leave a finance lead to work it out. If the ledger side matters as much as the Square side, our QuickBooks Power BI dashboard build is the usual companion.
Locations come from the Locations API rather than a typed list, so each site arrives with its own identifier, currency, status and timezone. That timezone is not a detail: a chain spanning three of them has three different answers to “what did we sell yesterday?”, and a model built on UTC alone will quietly misallocate the edges of every trading day. We build a real location dimension over the top — region, market, concept, open and close dates — so one slicer drives every page and roll-ups are not maintained by hand.
The catalog is modelled at item-variation grain because a variation is what actually sells. A coffee shop’s twelve-ounce and sixteen-ounce are one item and two variations, and a report built at item grain cannot tell you which is carrying the category. Items, categories, modifiers and taxes sit above the variation as the dimension hierarchy. Once the model is right, refresh is the easy part: the dataset is published to your workspace on a schedule that matches your trading rhythm, within Microsoft’s documented data refresh limits (opens in new tab) — up to eight scheduled refreshes a day on Power BI Pro, more on higher tiers — with row-level security so a store manager opens the report on their own site. The modelling discipline underneath is the same one we set out on our data modeling and DAX page.
You buy prepaid Flex hours that never expire: Starter 20 hours at $125/hr ($2,500), Growth 40 hours at $115/hr ($4,600), or Power 80 hours at $100/hr ($8,000). Hourly work starts from $100/hr. A first Square dashboard typically fits inside one block, and most builds ship in 3–5 business days once the scope estimate is approved. There is no project minimum and no retainer — you refill only when you want more.
Traditional Power BI firms charge $175–250/hr, set project minimums, want $10K+ to start, let unused hours expire, and don’t begin for 4–6 weeks. You send the request, we reply with a scope and hours estimate, and hours are deducted only for work you approve. Our affordable Power BI consulting page covers the pricing model in depth, and how prepaid Flex hours work walks through the mechanics.
Square is not one vertical, and this build is not written for one. It fits multi-location retailers who need item-variation mix and category margin across sites; counter-service and quick-service food businesses who care about ticket size, daypart shape and tender mix; salons, spas and barbershops where service revenue, tips and staff performance are the question; and mobile and field-service businesses invoicing from a phone. If your operation is a full-service restaurant with dayparts, menu engineering and comp-store sales at the centre, the industry-level treatment is on our Power BI for restaurants page, and groups running Toast should read the Toast POS Power BI dashboard build instead — different platform, different extraction, different measures.
Two things we scope explicitly rather than assume. Square Online stores sit inside the same account, so their orders arrive through the same Orders API and are separated by sales channel in the model — but if the online store is the business rather than a sideline, Shopify and WooCommerce dashboards cover that shape better. And Square Payroll, Square Loans and Square Banking are separate products with separate data; we will pull them in if you need them, but they are named in the scope rather than folded in silently. Contact is form-only — send your request and we reply with a scope estimate.
business days, typical
only scopes, app you own
own everything
A first Square dashboard usually fits inside one Flex block, and hours deduct only for work you approve. See Flex pricing →
How it works
We tell you exactly which OAuth application to create in your own Square account and which read-only scopes it needs, and in parallel we settle the definitions — which layer of the sales stack counts as revenue, how comps and discounts are treated, whether cash tips have a second source, how far back usable history goes. You get a scope and hours estimate before any work begins.
A senior US-based engineer builds the scheduled extraction across the Orders, Payments, Payouts, Catalog and Locations endpoints, lands it in a store you own, builds the star schema at the grains above, and writes the DAX — then reconciles every total against a Square report your team already runs.
Published to your Power BI workspace with scheduled or incremental refresh, row-level security so each manager sees only their locations, and documentation you keep. We maintain it from your banked hours, and you can revoke our Square access without touching the model.
Representative perspective
We had four numbers on the Square summary screen and four different people quoting a different one in the same meeting. Having each layer named, with the bridge between them on the page, ended an argument we’d been having for two years.Owner, multi-site specialty retailer
Representative perspective
Matching Square deposits to the bank was two days of every close. Now the deposit opens into the charges, refunds and fees that made it, and the part nobody warned us about — that cash tips were never in the tip figure at all — is stated on the report instead of discovered.Finance lead, regional salon group
Tell us how many locations you run, which Square products you use, and the reports you need. We’ll confirm the extraction route and the definitions, then tell you the hours before any work begins — drawn from prepaid Flex hours that never expire, with no project minimums and no obligation.
FAQ
Power BI Desktop ships no first-party Square connector, so data leaves Square through its published APIs under an OAuth application your business registers and owns. Three routes matter: the Orders API for order and line-item grain, the Payments API for tender, processing fee and refund detail, and Square’s Cube-based Reporting API for pre-aggregated measures. Seller Dashboard report exports are the fallback where an app cannot be approved quickly.
Because they count different things. Orders describe what was sold — line items, taxes, discounts, service charges and returns. Payments describe what was tendered, including tips, processing fees and the card or cash source. One order can carry several payments and one payment can span split tenders, so the two only agree once each is modelled at its own grain and joined on the order identifier rather than added together.
Square defines them as a stack. Net sales are gross sales less returns, discounts and comps. Gross sales plus taxes give total sales. Total collected is payments less refunds, and net total is total collected less fees and other withholdings. A dashboard that treats any one of these as revenue will disagree with your books, so we separate each layer into its own measure and reconcile the set against a Square report your accountant already runs.
No. Square’s own reporting defines tips as voluntary gratuity added to the transaction and states that cash tips are excluded because they are not recorded in the point-of-sale app. For a bar, a salon or any cash-heavy counter business that materially understates gratuity, so we either flag tips as card-only on the report face or blend in a tip declaration source you already keep.
Not directly, and no honest model pretends otherwise. Square charges a processing fee on the payment total, not on individual items, so a single sale spanning three categories carries one fee with no category attached. We model fees at payment grain, and where a per-category view is genuinely needed we allocate on an explicit, documented basis you approve — stated on the report as an allocation rather than as a fact.
Yes, and it is usually the sleeper request. Square’s Payouts API exists to reconcile the Square balance to the bank balance, and its payout entries carry gross, fee and net amounts per charge, refund, fee and adjustment. We model those entries so every deposit on the bank statement opens into the transactions, fees, refunds and disputes that produced it.
No. We are an independent US-based Power BI consultancy with no Square partnership to claim and no prebuilt connector to sell. We build against Square’s published, documented APIs using an application your business registers, owns and can revoke, and everything we produce is published into your own Power BI workspace.
If your question is the store rather than the terminal, Power BI for retail covers sell-through, basket and inventory across every channel you sell on. Full-service restaurant operators want Power BI for restaurants for prime cost and the weekly operating narrative, and groups on a different POS should read the Toast POS Power BI dashboard build, which handles Toast’s business date and menu-mix grain rather than Square’s. Where card processing is the whole business model, the Stripe Power BI dashboard page covers subscription revenue and MRR, which Square’s order-shaped data is not built for. Online-first sellers are better served by Shopify or WooCommerce dashboards, and the books join through the QuickBooks Power BI dashboard build. Every task is scoped before work begins from your prepaid block of senior-led hours — no project minimums, no change-order surprises, and hours are deducted only for work you approve.
Book a free demo
Tell us the task. We scope it, you approve, and a senior consultant delivers — typically in 3–5 business days. Prepaid hours that never expire.
The fastest way to reach us is the form — tell us the task and we'll reply within one business day.