Shopify order sales by location get double counted in Power BI

[Anonymous]

Hi Community,

This is a specific problem relating to Shopify data in Power BI. It is in a way more of a Shopify problem than a Power BI one; however, outside of Power BI, the Shopify data (exported from Shopify front end to Excel) is fine. Shopify connects to Power BI via an OData feed. 

I have not been able to locate anything online that can help with this, so thought I'd post it on here, in case one of you can: 

Many of our Shopify orders consist of products stored in different locations; one complete and payed for order could consist of a dress listed by one location, and a pair of shoes listed by another. When we try to report on sales amount by location in Power BI, the orders that contain products from more than one location get attributed to all these relevant locations. So, whereas the money for the aforementioned dress should be attributed to location 1, and the money for the shoes to location 2, both sales get attributed to both locations. The overall total sum is still right, because Power BI cleverly automatically dedupes this number by order ID, but we are unable to report acurate sales numbers by location. As far as I can tell, this would be possible if sale amount was somewhere attributed to products, but the only way products are linked to amount is via orders, so the same problem arises. 

In the same vein, I also have a problem with connecting products (i.e. items listed on wesbite) to a date filter, and connect the orders and sales amount to a date filter, again because products and orders seemingly are so intrinsically linked within the Shopify data structure. 

I hope this makes some sense. If anyone thinks they may be able to help, feel free to ask for more details or clarification if needed. 

Thanks in advance!

Ann

[ReportMaster]

Hi, were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supemetricsn  and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Shopify connector in the data sources list:

After that, you need to follow instructions and install the windsor.ai app from the Shopify App Store:

then on preview and destination page you will see a preview of your Shopify fields

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.

[Anonymous]

Hi again. Just in case someody peruses thes entries in the future, I found that the solution is to link on lineitem ID. This is much more specific 'Shopify in Power BI' issue than it is a Power BI issue, so will leave it there and invite anyone who has issues with this now or in th future to get in touch. 

[Anonymous]

Hi again, Winniz. Thanks so much again for engaging in this. Yes, you pretty much summarised what the problem is: one order often contains products from different vendors, so orders are assigned to multiple vendors. I am still exploring other tables in the Shopify data structure, but so far I have not been able to find anywhere where orders or sales can be assigned by vendor. The clue might lie in one or some of the line items tables (not sure how familiar you are with Shopify data) but it is by no means obvious. If I find a way, I will post it here!

[Anonymous]

Hi Winniz/@v-kkf-msft, thanks a lot for getting back to me! I've inserted two tables below: the first, smaller one is sales amount by vendor (aka location) and in total over a certain time period. The second table shows the same info but with order ID included, mainly in order to illustrate that some order ID are duplicated across more than one vendor. The total by vendor is the same in both the first and the second table, and each vendor's amount summarised superseeds the total. Hopefully this illustrates that, including order ID in the table or not, the amount by vendor is not usable. My earlier point that if we could attribute each product's sale to vendor, it would be possible to attribute actual sales by vendor, but so far I haven't been able to link products and sale amount without involving orders (to help with this I believe you'd need knowledge of the Shopify data structure. I am still exploring it so might find a way around it). I hope this is relatively clear - let me know if you need more info. Thanks again, Ann

[v-kkf-msft]

Hi @Anonymous ,

I have performed some simple tests, and I am not sure I understand correctly because I cannot perform detailed tests due to the conditions. When a customer buys multiple products at once, there may be multiple vendors for one order ID, right? That is, would multiple vendors exist for one order in shopify? If so, could the amount of the order be assigned to per vendor in shopify?

Best Regards,
Winniz

[v-kkf-msft]

Hi @Anonymous ,

Don't clear your data model. Could you please share your sample data without sensitive data? What is your expected output? 

Best Regards,
Winniz