Lightspeed and Power BI: revenue, margin and stock per store

What can you already report in Lightspeed itself?

Don't start with Power BI before you know what Lightspeed already offers. According to Lightspeed's product information on Retail X-Series (checked on 9 October 2026), reporting depends on your plan:

• Basic reporting (Lean and Standard): including a sales summary, payments, register closures, gift cards, store credit and tax.
• Advanced reporting (Advanced and Enterprise, or as an add-on): including a retail dashboard, inventory summary and performance, sell-through, dusty inventory and sales by the hour. According to Lightspeed, reporting per outlet, gross profit and margin are part of this advanced tier.

If you have one store and mainly want to know what sold today, that is often enough. So check what your plan offers before you build anything.

When is Power BI worth it alongside Lightspeed?

The reports in a point-of-sale system look at the point-of-sale system. That makes sense, but it starts to pinch as soon as your questions go further. Power BI mainly adds value when:

• you want to compare stores fairly. Not just revenue, but revenue per transaction, margin and discounts per store, in one overview with the same definitions.
• you want to combine till data. With your accounting (costs, rent, staff per store), your online shop or your time tracking. Only then do you see what a store really earns.
• you want to keep history. An API often only shows the current state, for example of stock levels. If you want to see how stock developed over months, you need to store snapshots yourself.
• several people need to see the same numbers. Store managers, purchasing and management, each with their own filter but one version of the truth.

Still unsure whether you need a dashboard at all, or whether Excel will do? Read Excel or Power BI first.

Which Lightspeed data do you need for a store dashboard?

For revenue, margin and stock per store, you broadly need four types of data from Lightspeed Retail:

1. Sales and sale lines. Amounts, quantities, discounts, tax and time. This is the basis for revenue, transactions and average basket value.
2. Products. Item, brand, category and the cost price as recorded in Lightspeed. Without a reliable cost price there is no reliable margin.
3. Inventory. How much of each item is on the shelf in each store.
4. Store and register context. Which sale belongs to which store and which register, so you can compare.

You can add customer data if you want to see how many customers return, provided it is linked to sales and you have the rights to use it. Exactly which data is available depends on the API, your plan and user permissions. Our page on the Power BI Lightspeed integration explains how we map that out per client.

Which KPIs belong on a retail dashboard per store?

Keep the overview small. Together, these six numbers give a good picture of how each store is doing:

• Revenue versus last year, per store and per week. Compare with the same week last year, so seasons and holidays don't mislead you.
• Number of transactions and average basket value. Is revenue falling because of fewer customers or smaller purchases? Each calls for a different response.
• Gross margin in euros and percent. Revenue minus the cost of goods sold. A store with high revenue and heavy discounting can earn less than a quieter one.
• Discounts and returns as a share of revenue. If this rises in one store, you want to know why.
• Stock value and days of inventory. How much money is sitting on the shelf and how many days of sales that represents. Too much is idle cash, too little costs revenue.
• Sell-through per category. The share of purchased stock sold in a period. Useful for seeing which store sells which collection well.

Define how each KPI is calculated: does a return count in the week of the sale or the week of the return? Is revenue including or excluding VAT? More on choosing core metrics in the 10 KPIs every SME dashboard needs.

What does such a dashboard look like in practice?

A fictional example to make it concrete. A shoe and bag retailer with four stores in Brabant uses Lightspeed Retail and a separate accounting package. Every Monday the owner emails the store managers an Excel file he has put together himself from exports.

In Power BI that becomes one report with three pages:

• Overview: per store, last week's revenue against the same week last year, the number of transactions, the average basket and the gross margin in percent, side by side in one table.
• Assortment: margin and sell-through per category and brand, with a store filter. Here you see, for example, that a brand sells well in one store and sits on the shelf in another.
• Stock: stock value and days of inventory per store, with a list of items that have not sold for weeks. Purchasing uses that list to move stock between stores before marking it down.

Each store manager sees their own store by default; the owner sees everything. The Monday meeting is no longer about which number is right, but about what to do about it.

Step by step: connecting Lightspeed to Power BI

1. Define the questions and the KPIs. What do you want to see every week, and who uses it? Pick three to six KPIs and write down the definitions.
2. Arrange API access. Lightspeed Retail X-Series offers personal tokens for an individual retailer and add-ons for developers who connect multiple retailers. Give the integration read-only access.
3. Load the data into your own data layer. Don't let Power BI read the entire API every time. Fetch the data, store it in a database and afterwards refresh only what is new or changed. That also lets you keep stock snapshots.
4. Build the data model and reconcile with Lightspeed. Compare revenue per store per week with the reports in Lightspeed. Only move on once they match.
5. Build the report with real users. Have a store manager look at the first version. They spot what is missing faster than any design on paper.
6. Arrange refresh, checks and ownership. Refreshing daily or a few times a day is plenty for most retailers. Make sure someone gets an alert when a refresh fails.

Where does it usually go wrong?

• Rate limits. According to Lightspeed's documentation (checked on 9 October 2026), the X-Series API uses a limit per five-minute window of 300 times the number of registers plus 50 requests. With one register that is 350 requests. Plan your first full history load accordingly.
• Missing or outdated cost prices. If cost prices are wrong in Lightspeed, your margin is wrong. You don't fix that in Power BI but in the source.
• Messy categories. The same category under three different names makes comparing assortments difficult. Agree on a fixed structure and clean it up once.
• Returns, credit notes and deleted records. Test deliberately with exceptions, not just with regular sales.
• Multiple Lightspeed products. Lightspeed has several product lines, each with its own API. An integration for Retail X-Series does not automatically work for another product line or for the online shop.

If the same data also feeds other reports or workflows, a central data foundation is often the smartest place to store it.

Need help with your Lightspeed dashboard in Power BI?

Want to see your stores side by side on revenue, margin and stock, without merging exports every week? We are happy to look at your Lightspeed setup and your other systems. Read more on our page about the Lightspeed integration with Power BI, see what we do in dashboarding or read how we work.