Are you using Acumatica?
Have you heard about Power BI, but aren’t sure how to get started?
In this blog post series, my goal is to help you get started.
Here’s the plan:
- I create a working Power BI example that is connected to Acumatica via Query Quarry.
- If you want to try it on your own Acumatica data, you can install Query Quarry together with the Power BI Desktop file at the bottom of this post.
Sound like a good plan?
Great! Let’s get started.
Today’s Example – Finding Unused Inventory
Have you ever looked at the stuff sitting in your warehouse and wondered, “when was the last time that someone actually ordered that?”
It’s so easy for the Purchasing department to buy stuff, then, oops, the customer’s order gets canceled, and, bummer, now you have excess inventory.
Getting product out to the customers is always the #1 priority. And it can be difficult to get the Inventory department to care about excess Inventory.
Hey, as long as we still have room in the warehouse, everything is fine right? Well, not if you’re trying to do a better job of managing your precious cash.
One of the strengths of Power BI is that you can combine different sets of data together in one report.
In this Power BI example, I created a simple table that pulls from two places which are the first two columns:
- Total Inventory Valuation: This column pulls the inventory valuation numbers from the same place as the Inventory Valuation report in Acumatica.
- Most Recent Order: This looks at all of the sales order lines across all order types and finds the most recent dated order.
- Days Since Most Recent Order: Not a separate data source. Just the difference between the Most Recent Order column and today’s date.
Pro Tip: You can also filter on specific warehouses using the Warehouse slicer on the right-hand side.
Note: Currently this includes Non-Stock Items which is kind of useless. I can filter them out, but I didn’t notice it until I was writing up this post and I just want to get this post published. So, if anyone is interested I’ll filter them out. Otherwise, I’m just going to leave it as-is for now.
Connect This to Your Acumatica Data
Are you curious to see what this Power BI example looks like on your Acumatica data?
First, you need to download and install Query Quarry since this example is based on Query Quarry. Click here and scroll to the bottom for instructions.
Second, if you don’t already have Power BI Desktop installed, you need to download it from Microsoft. Click here and then click the DOWNLOAD FREE button. Run the install after it’s downloaded.
Third, you need to download the .pbix Power BI Desktop file used in this example. Click here to download.
Fourth, open the .pbix Power BI Desktop file used in this example in Power BI Desktop and make the following changes to point to your data:
Click Edit Queries -> Edit Parameters
Change the Instance parameter to your Acumatica Instance URL.
Change the Company parameter to your Acumatica Company. It should match the company name shown after the @ symbol in the upper right-hand corner when you’re logged into Acumatica. If there is no @ symbol after your username when you’re logged in, then it means that you only have one company in your Acumatica instance so you need to leave the Company parameter empty.
Click OK
Click Refresh
If prompted, choose Basic, then enter your User name and Password, then click Connect
You should now be looking at this Power BI example with your own data. Cool huh?