How do you calculate net sales with Power BI Connector?

Shopify Apps

Topic summary

Goal: derive “net sales” in Power BI from Shopify data via a third‑party connector. The built‑in Shopify “Sales over time” metric doesn’t map cleanly to a single field; presentmentMoneyAmount from orders_originalTotalPriceSet matches closely but is missing values in some months.

Suggested approach (from experience with another Power BI connector): compute net sales from line items, discounts, and refunds rather than rely on one field.

  • line_item__quantity — quantity (includes returns)
  • line_item__price — product price before discounts/returns
  • line_item__discount_allocations — JSON path: [0].allocatedAmountSet.shopMoney.amount
  • order_refunds_net — total refund amount incl. tax
  • order_refunds_total_tax — tax amount in the refund

Calculation: for each line, quantity × price minus the allocated discount; then subtract refunds at the order level. Refunds are only available per order (not per line), and partial returns occur, so allocate refunds across the order’s products proportionally as an approximation.

Notes: screenshots with the JSON path and a full formula are provided and are central to implementation. Outcome: no definitive single-field equivalent to Shopify’s “Sales over time”; a calculated, approximate method is recommended. Discussion remains open.

Summarized with AI on December 24. AI used: gpt-5.
1

Hello everyone,

I am using the Alpha Serve Power BI Connector to build up a Power BI report for the owners of this shopify account. Everything works pretty well, but I can’t figure out which field I have to use to calculate the net sales. Shopify offers various fields to calculate the sales amount, but none of them matches 100% with the sales over time statistic shopify offers.

What is used there?

I found out that „presentmentMoneyAmount“ from the table „orders_originalTotalPriceSet“ fits really good, but it is not filled for every month.

I hope one of you knows the solution for this problem. Thank you and happy holidays.

Greetings

2

I haven’t used the Alpha serve connector but I used the one from Vidi Corp. Here is how I calculated net sales.

The following fields are needed in order to calculate net sales:

[line_item__quantity] – quantity including returns

[line_item__price] - product price before discount and returns.

[line_item__discount_allocations] - JSON field from which you can get a discount for a certain product: [line_item__discount_allocations].[0].[allocatedAmountSet].[shopMoney].[amount] check screenshot below:

Eugene_Fathom_0-1721404405409.png
Eugene_Fathom_0-1721404405409.png699×281 15.2 KB

[order_refunds_net] – the total refund amount including tax

[order_refunds_total_tax] –total tax amount for the refund.

To calculate NetSales you need to multiply quantity * product price for every row of data and then subtract discount amount and refunds

There are complexities here because refunds are shown on order level rather than order line level which means you can’t allocate them to products. Sometimes you have partial returns. The only way of allocating refunds to product is to divide them between the products in this order.

This is not the cleanest way but it is approximately right

Вот формула:

Eugene_Fathom_1-1721404405408.png
Eugene_Fathom_1-1721404405408.png1272×219 21.1 KB