By using this website, you agree to our Terms of Use (click here)
This post shows you how to create one of the examples from my presentation at Acumatica Summit 2019 in Houston, entitled C103: Getting Actionable Information with Self-Service Reports & Dashboards (click here for all of the examples).
In this Example #8, I'd like to give an introduction to Power BI.
The best way that I know to introduce Power BI is by using Microsoft Excel 2016. The reason is that the Power BI engine (the most important piece of Power BI) is already included for free in Excel 2016.
In Example #7 we used the Data Model and created a relationship between the tables. We used the Power BI engine to do that, without even realizing it.
Now, in this Example #8, we can tap into the real "power" in Power BI by creating Measures.
Measures are the real "power" in Power BI.
1. Let's take the Excel file that we created in Example #7. Here's the link again to download it:
2. I'm just going to add one thing to illustrate the power of Measures. It's pretty easy to analyze Sales. We did that in Example #7. All we had to do was drag Order Total into the Values area like this:
3. When you do that, you have some options. You can click Order Total -> Value Field Settings... like this:
4. But your options are limited. The default in this case is to Sum the Order Total (which is what we wanted), but there are 9 other options (Count, Average, Max, Min, StdDev, StdDevp, Var, Varp, Distinct Count). Which is nice, but you're still limited to only 10 options.
5. You can also click the Show Values As tab to decide how the option you picked on the Summarize Values By tab will be displayed. You have 6 options (No Calculation, % of Grand Total, % of Column Total, % of Row Total, % Of, % of Parent Row Total)
6. So it's nice to have options. And you can do a lot with these options. But Measures (the real "power" in Power BI) drop a nuclear bomb on these options. You are used to writing Excel formulas using Excel functions. But Measures give you the ability to write formulas for the Values area of a Pivot Table. It's hard to exhaust the reasons why this is a big deal. But, trust me, it's a big deal. When you create Measures, you write formulas in a formula language called DAX, but it's very similar to Excel formulas which is another reason why I think it makes sense to introduce Power BI within Excel. It's already familiar to Excel users. To create a Measure, just right-click on a Table and click Add Measure... like this:
7. Let's start by doing exactly the same thing that we did when we dragged Order Total to the Values area, but this time let's do it with a DAX formula. This formula is the equivalent of what we did in Step #2. Also notice the formatting options that you get on the bottom of this screenshot.
=SUM('SO-SalesOrder'[OrderTotal])
8. Notice in the screenshot above that we aren't limited to the 10 options that we had in Step #4. We can write any DAX formula that we want. This is VERY POWERFUL. If you are an Excel user and you want to learn how to start writing DAX formulas, there is no better resource than the book Power Pivot and Power BI by Rob Collie & Avichal Singh:
https://powerpivotpro.com/the-book/
9. That book will take you step by step through learning DAX from the perspective of an Excel user. For this example, I'm going to take you on a rocket ship and drop 2 more Measures on you. First, let's create one called SalesNewCustomers:
Note: I'm not smart enough to come up with this Measure. I stole it from the book The Definitive Guide to DAX (click here).
=VAR CurrentCustomers = CALCULATETABLE('AR-Customers','SO-SalesOrder')
VAR PreviousCustomers = CALCULATETABLE('AR-Customers',CALCULATETABLE('SO-SalesOrder',FILTER(ALL('Calendar'),'Calendar'[Date] < MIN('Calendar'[Date]))))
RETURN
CALCULATE(SUM('SO-SalesOrder'[OrderTotal]),EXCEPT(CurrentCustomers,PreviousCustomers))
10. Then, let's create one more Measure called % of New Sales:
11. Notice that the % of New Sales measure references the SalesNewCustomers and SalesTotal measures. Yes, you can reference measures in other measures, just like you can do with Excel formulas by referencing cells that have other Excel formulas. Let's drag the % of New Sales measure into the Values area on a Pivot Table:
12. What are we looking at in that Pivot Table? What does % of New Sales tell us? Well, before we were just totaling Sales. Now we are calculating how much of those total Sales were from Customers who are buying something from us for the first time. We might be able to easily see that our Sales are increasing and that the number of Customers are increasing, but now much of our Sales in each period were from 1st time Customers. That's what % of New Sales tells us. For example, take a look at the 2013 column in the screenshot above. That's the first period we have sales, so of course all of our Sales are from 1st time customers. But then take a look at the 2014 column in the screenshot above. We can see that 65% of our Large Order Size Sales were from 1st time Customers, 44% of our Medium Order Size Sales were from 1st time Customers, and 1% of our Small Order Size Sales were from 1st time Customers. This is pretty insightful. Note that I need to correct my DAX formula to get the Grand Total to display correctly.
13. The cool thing about Measures is that they work at any level of the Pivot Table (as long as you write the DAX formula correctly). For example, we can expand 2014 to see the individual Months (or Quarters, etc.) and the % of New Sales measure still works. Time-based analysis like this is much more pleasant in a Business Intelligence application than in traditional reports. Pretty cool huh?
14. So that's all I've got for an introduction to Measures. It's one of those things where you either scratch the surface or dive deep. Trust me, we're just scratching the surface here. You can download the Excel file that has this additional Pivot
That's is for the example of Power BI in Excel. But what about the real Power BI? What does that offer?
The main difference between Excel and Power BI is that Excel is more for "numbers" people while Power BI is more for "visual" people. Personally, I was an Applied Mathematics major in college and an Accountant (CPA-Inactive) by trade. So I'm a "numbers" person. But most people are "visual" (I think).
Power BI lets you take your Excel Pivot Table work and make it easier to consume for "visual" people.
Let's take the work that we did in Excel and make it available to PowerBI.com.
1. To get started, anyone can go out to PowerBI.com (click here) and create an account for free. You can also download Power BI Desktop to create Power BI reports. It's just like Report Designer with Acumatica. You use Power BI Desktop to create the reports, but then you save them into PowerBI.com where you can run them using your web browser or the Power BI Mobile app. You can do all of this for free. But as soon as you start sharing your reports with others, then you pay $10/user/month. Which makes sense right? You can try it out, make something cool, and you only pay when you realize that your work is worth sharing with other people.
2. To design Power BI reports, download and install Power BI Desktop here:
3. We don't have to start from scratch. From within Power BI Desktop, we can import the Data Model from our existing Excel model. Just go to File -> Import -> Excel workbook contents like this:
4. I'm going to import the Excel with Power BI.xlsx (click here) file that we created earlier in this example:
5. Then just click the Start button:
6. Power BI will extract the Data Model from the Excel file which includes all of our Power Query work and Relationships from Example #7, including the Measures that we created here in Example #8:
7. You now have a blank canvas to put your Visuals on. Power BI has A TON of options for Visuals. Let's start with the familiar Pivot Table from Excel which is called Matrix in Power BI. Then we can drag fields from out Data Model in the Fields area into the Rows, Columns, and Values areas just like we're used to doing with Excel Pivot Tables. I really like how the tables highlight in yellow on the right-hand side when you use fields from them so you can easily see which Tables and Fields are being used by a Visual.
8. You can use the Paint Roller icon to set formatting options on a Visual. In this case I put a border around it and set the Style to Bold header:
9. Since I'm not a "visual" person, I don't get too heavy into Visuals, but there are a lot of options. You can explore the out-of-the-box options in the Visualizations area or click ... -> Import from marketplace to browse additional Visuals. Power BI has a VERY strong community and the marketplace of Visuals continues to grow at a steady pace. Note that some of the more advanced Visuals require you to pay money:
10. When you are ready to publish your report to PowerBI.com, just click Home -> Publish and sign-in with your PowerBI.com account. You can now view your report using your web browser or the Power BI Mobile app in the iOS/Android store. If you then want to share your report with other PowerBI.com users, that's when you'll need to pay $10/user/month:
11. Once a report has been published to PowerBI.com, you can actually embed it back into the Acumatica menu structure so people don't feel like they are leaving Acumatica and going to an outside application (even though technically they are). Just go to the report in PowerBI.com and click File -> Embed like this:
12. Take the URL in the first field and copy it to your clipboard:
13. Now we need to add that URL to the Acumatica menu which, if you're using the Modern UI in Acumatica, requires two steps. First, you have to create a Site Map entry using the Site Map (SM200520) screen. This is how you will control security to this Power BI report, who will see it on their Acumatica menu. Put it wherever you want in the Site Map menu structure (for some reason I chose Shared Dashboards -> Dashboards -> CRM -> Sales by Zip Code), make up a Screen ID (just make sure it isn't already being used, I used PB.00.00.01), choose a Title, and paste the URL that you just copied into the Url field:
14. Second, now you can add the Site Map entry to a Workspace. Just do ... -> Edit Menu, click the Workspace you want to add it to (I chose Dashboards), click ADD MENU ITEM, search for the Site Map entry you just created, then click the ADD & CLOSE button:
15. Note that you can drag it around to a different section of the Workspace if you want. I dragged mine to the Learning by Example area that I created for this presentation:
16. Then click it to run the Power BI report and have it appear within Acumatica. Note that I used another Power BI report that I have because I think it looks nicer than the one we created above. The only problem is that I'm typing this on my flight on my way to the Summit in Houston and the screenshot isn't that great on my small laptop screen. This report is fully interactive, just like it would be if you ran it from PowerBI.com on your web browser or from the Power BI mobile app. You can click on Visuals and have them filter other Visuals, you can click on the bottom to view another tab (we're looking at My Dashboard in this screenshot), and you can even use the Filters pane on the right-hand side:
17. Here is a screenshot of my Map tab in case you were wondering what that looked like:
Note: It used to be A LOT harder to embed these Power BI reports in Acumatica, but there are still reasons why you might want to use the "harder" method. For more on that, checkout this post (click here).
Ok, so that's it for Example #8 and my introduction to Power BI. I love how you can start in good old familiar Excel to do the hard part of building the Data Model, add Measures to leverage the real "power" in Power BI, import the Excel Data Model into Power BI Desktop to make it more appealing to "visual" people, publish it to PowerBI.com so it can be consumed in your web browser or the Power BI Mobile app. All that FOR FREE. Incredible huh? Then, if you want to share your report with others, you just have to pay $10/user/month which is very reasonable.
Lastly, I want to leave you with a few interesting links aimed at business people (not data geeks). These are very thought provoking:
1. Here is the link again to the #1 book on Power BI. This is required reading for anyone looking to get into Power BI. It is the most entertaining, hard to put down, technical book that I have ever read:
https://powerpivotpro.com/the-book/
2. Buried in this post is a gem of a statement that I'm going to embellish a little bit based on what I've heard the author say in person back when I used to attend a Power BI User Group in Cleveland (I'm third from the left in the bar picture on the bottom, click here). Ok, here goes the statement:
Power BI (formerly called PowerPivot) allows you to create numbers (using Measures) that "have never existed before" in your organization (because they were too difficult to calculate and deliver in a timely way):
https://powerpivotpro.com/2009/10/what-is-powerpivot/
3. This insightful post explores the Much-Overlooked Producer/Consumer Dynamic when it comes to how information actually gets delivered in organizations:
https://powerpivotpro.com/2014/04/the-much-overlooked-producerconsumer-dynamic/
4. Speaking of insight, this post is a must read. This one is especially great for executives who know nothing about Excel, reporting, data, etc.:
https://powerpivotpro.com/2017/01/ten-things-data-can-2017-aka-ten-things-power-bi-can-2017/
5. And if your boss prefers a video presentation over a written post, here is a 50 minute YouTube presentation of the previous post in #4:
Want to join the conversation? Feel free to add a Post below to this Topic to continue the conversation.
Thank you, Tim, for sharing it with us, this is a fantastic idea because Report Designer is very limited.