How to accurately measure sales in Power BI?

Shopify Plus
1

Hello,

I brought my shopify data into Azure so that I can pull it into Power BI. I’m now creating reports, but for some reason, my sales are not matching Shopify. Shopify measures sales in weird and mysterious ways. Has anyone been successful in creating a sales measure in Power BI that you’re confident in?

Thanks!

2

Hi - Yes

I have broken the data into 2 groups, 1 being the payments and refunds (which matches closest to the Shopify revenue) and the other being product sales.

On the payments side, shopify recognises the revenue on the day that it happened, ie you have an order from 2 weeks ago (or 2 months) where a refund is given or a stock item is returned, that is shown on shopify as having happend today. If you work everything at an ordered date level you will not balance. Shopify also has shipping on the order header level which needs to be taken into account

In power BI i also have a sales by product level which shows which products sold on the days as well as takes care of product returns

Best way of balancing is to get a sales report at the day level - by order by product and check your balancing with that

Thats how I have managed to get it working (there might be better ways)

3

Thanks. So, I did figure out that I needed to look at the refund date and subtract the refund from sales for that refund date and not the order date. I don’t understand the payments as well though. I’ve learned that my staff have managed to refund money back to the customer without actually returning any product. How do you handle that situation? At this point, I’m just looking at the order lines to calculate sales, so it wouldn’t pick those up, but do I also need to look at the payment transactions? Or, do you not have that issue?

The other thing I’m not sure about is the Exchanges and Order Edits. Shopify told me they are summarized in the Sales Agreement endpoint. Have you been using that? For example, if I exchange product X for product Y, I would see a refund come through on that date for product X, but product Y (a new sale) would be attributed back to the original order date unless I looked at this new sales agreement entity which calls out the date of the exchange.