AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Rolodex
  • Login
  • Start Here
  • Courses
  • Register

Getting Started with Acumatica and Power BI – Part 1

October 31, 2017 by Tim Rodman

Getting Started with Acumatica and Power BI. In this example, comparing # of Customers, # of Invoice Lines, and Total Sales Amount in your Acumatica data.

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:

Getting Started with Acumatica and Power BI - Part 1

 

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:

Getting Started with Acumatica and Power BI - Part 1

 

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

Getting Started with Acumatica and Power BI - Part 1

 

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:

Getting Started with Acumatica and Power BI - Part 1

 

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?

Filed Under: Query Quarry Tagged With: Acumatica, Acumatica Blog, Acumatica Learning, Acumatica Reporting, Acumatica Training, Getting Started with Power BI, Power BI

By using this website, you agree to our Terms of Use (click here)

Online Members

Recent Blog Posts

  • EP 37: Jody Lorincz, IT Manager at MHR Brands, sharing his Acumatica Customer Story (Podcast) January 13, 2021
  • EP 36: What Motivates ERP Veteran Nicole Ronchetti To Participate in Acu-Connect (Podcast) January 8, 2021
  • Acumatica and Scanco WMS – A Cautionary Tale for ISVs January 6, 2021
  • EP 35: Ideas for AUGForums.com Live with Ryan Brown (Podcast) December 12, 2020
  • EP 34: Talking Acumatica eCommerce and the Next Normal for Distribution, Wholesale, and Manufacturing with Ted Stenstrom (Podcast) December 11, 2020
Acumatica Learning Resources

Recent Forum Posts

  • RE: Construction Module/Purchase Orders - Commitments

    Do you see them in the Commitment tab of the project? I...

    By johnymt, 1 day ago

  • RE: Compressing Images

    That would be cool. Not that I know of personally. Are ...

    By Tim Rodman, 2 days ago

  • RE: Support for custom fonts

    @ckwiat That's what I've experienced with MICR which is...

    By Tim Rodman, 2 days ago

  • RE: Support for custom fonts

    I actually came across this that I will try:

    By Cory, 2 days ago

  • RE: Support for custom fonts

    @timrodman do you know if anything was definitely figur...

    By Cory, 2 days ago

  • RE: Adding bill description on check stub

    @cfritsch Chip great modifications. Just a quick questi...

    By Jswartz, 2 days ago

  • RE: Setting Up System Accounts with Individual Email Accounts for all Users

    Cory, Did your company end up making a decision o...

    By nsmith, 2 days ago

  • Problem when i have more then one Alternate ID

    Hi, I have Stock Items that includes more than one A...

    By Fosse, 3 days ago

  • Best way to Learn Microsoft PowerApps

    Hi All, I recently started PowerApps Training at Mind...

    By Steveskok, 3 days ago

Recent Tweets

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.