Getting Started with Acumatica and Power BI – Part 1

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:

1. I create a working Power BI example that is connected to Acumatica via Query Quarry.
2. 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 – Comparing # of Customers, # of Invoice Lines, and Total Sales Amount

Have you ever tried comparing two numbers together?

Let’s take 5 and 3,500. No problem right? Your brain sees the 5, it sees the 3,500, and you can tell that they are far apart.

But what happens if you try to compare two columns of numbers across 12 months? Now you have 24 numbers to look at. Maybe something like this:

 

Great. Lots of numbers, but it’s kind of hard for my brain to wrap it’s “head” around them all.

What you really need is a graph to keep your brain from getting overwhelmed by too many numbers.

Graphs (sometimes called Charts or Visuals) are great because they give you a way to take in a lot of numbers without making your brain hurt.

If you were to graph the first column of numbers above, it would look like this:

 

If you were to graph the second column of numbers above, it would look like this:

 

Ah, much better. I can quickly “see” how the numbers change over the 12 months.

But graphs do have a problem. They only have 2 dimensions. There is an X Axis and a Y Axis.

The problem comes in when you have two very different sequences of numbers.

Take a look at the Y Axis on the two graphs above. The Y Axis on the first graph starts at 0 and ends at 16. The Y Axis on the second graph starts at 0 and ends at 6000. Very different!

This becomes a problem when you try to graph both columns of numbers on the same graph. It looks like this:

 

What happened to the first column of numbers? Well, I made it red so it would stand out. You can see it on the very bottom.

Why is it on the bottom? Because the largest value is 14, but the Y Axis goes from 0 to 6000, so the 14 basically looks like 0 on the graph.

How do we solve this problem? We can “index” the values by calculating each value as a percentage of the largest value.

To illustrate this, I created a Power BI example based on the Acumatica SalesDemo database.

This Power BI Example compares three things: # of Customers, # of Invoice Lines, and Total Sales Amount.

Here is the Power BI example. And it’s interactive so you can hover over the points on the graph or change the year down below to see what the data looks like in different years.

Pro Tip: Hold down the Crtl key on your keyboard to select multiple years above.

 

A few things to note about this Power BI example:

1. This graph is based entirely on Accounts Receivable Invoice, Debit Memo, and Credit Memo documents, without any filter applied to the statuses which means that unreleased documents are included.
2. Notice that the Y Axis goes from 0.0 to 1.0. Since every value has been “indexed” based on the largest value, the largest value is 100% (or 1.0) of the largest value which is why 1.0 is the largest “indexed” value.
3. Because of the consistent Y Axis, you can now see how the three things are “correlated” which means how they are related to each other. If you wanted to get fancy, you could start applying statistics, but your brain can do a pretty good job of understanding the correlation just by looking at the graph.
4. In 2013, it looks like we did our largest amount of sales in December, but we didn’t make sales to our largest number of customers or largest number of invoice lines that month. Which makes me wonder, maybe we need to focus on making larger sales rather than just selling to more customers.
5. In 2015, the rise and fall of Total Sales Amount appears to be more related to # of Invoice Lines than to # of Customers.
6. This example is live-connected to the Acumatica SalesDemo data and you can see that it was last refreshed on October 28th, 2017 at 10:44:52 PM.
7. I wasn’t smart enough to come up with this example. It came from the book Power Pivot Alchemy by Bill Jelen and Rob Collie which I highly recommend if you want to get into Power BI.

 

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?