In this short demo video, our Power Platform Capability Manager takes a common scenario – a sales team using Dynamics 365 and a service team using Excel – and shows how Power BI can pull all that data into a single view for actionable and interactive insights. Check it out!
Never Miss a Video, Subscribe Now!
Venkat: When it comes to reporting in actionable insights, no one wants to go to these multiple sources to find information that they’re looking for. This is where Power BI really helps.
Here’s the background for this report: our sales team works out of Dynamics 365. That’s where they keep track of all of their accounts, and all the opportunities that they work with. Our customer service team that actually creates orders works out of Excel. Now, immediately, we have two different departments who are using two different data sources.
Within Dynamics 365, we are looking at our accounts. Similarly, there’s all these opportunities, and now, in an Excel file, this is where we have all the orders that have been already created. We will try to create a report that gives us one consolidated view of all of this information.
Once I have my Power BI desktop open, I’ll go into Get Data, and just to show you what kind of sources I can connect to, I’ll click More. This opens up a new window that shows you a list of all the sources that you can connect to. If your source is not listed here, you can create custom connectors as well.
For our example, we’ll start with Dynamics 365. I say Connect. To get the web API URL, I go back to Dynamics 365. Under Settings, I go to Customizations. Under Customizations, I have Developer Resources. I get the web API URL, which is this very first option right here.
Once I’ve copied the web API URL, I’ll go back to Power BI desktop, and paste that URL here, and click Okay. Now, once it connects to Dynamics 365, it’s going to show us all the entities that are in our instance. From this list, I’ll say Accounts. I can just search for Opportunities. We can just hit Load.
Before we start creating our report, we will go into this Data tab, and then click the Accounts query. Once I click that, I hit the Edit Queries button. Now, my account entity has lots and lots of fields, but for my report I only need a few. For that reason, I will come in here, and I say Choose Columns, and choose the ones I really need. From this list, I uncheck Select All. I’ll start with Account ID, and then I’ll choose Name, City. We will choose State, which is this, Postal Code, or the zip code, as well, which is this field, Number of Employees. Let’s look for some revenue as well. I do need Account Number.
I think my account query is ready, so now notice that the list of fields is much, much shorter. I’ll do something similar for Opportunities. Go in here, and I go to Choose Columns. Undo Select All. We’ll start with the ID field, Estimated Revenue and Estimated Date fields, which are these right here, Actual Revenue and Actual Close Date, which is these right here. I think I’m good to go.
Now that our data is loaded, let’s really quickly look at relationships as well. I’m going to go into this Relationships tab, right here. Now, notice that between Accounts and Opportunities, we have a relationship here. It’s a one to many relationship, and this is something that Power BI has detected automatically. But in some cases, if Power BI fails to detect a relationship, you can easily create one by clicking the Manage Relationships button right here.
What we now need for our example is another data source, which is Excel. I choose Excel, and then I am going to go into the file that I’m looking for, which is this right here, and now it shows me a preview of the data, just like it did with Dynamics 365. If it looks fine, I’ll just click Load.
Now, you’ll notice here that in our relationship view, we have the Excel model loaded as well, and also notice that it knows that there’s a relationship between accounts from Dynamics 365 and the accounts from the Excel data. How is the relationship established? It’s between account number here, in Excel, and account number in Dynamics.
Now that our data model is prepared, we are now ready to build reports. Let’s see how.
To build reports, I will go into the Report tab right here. Once I do that, i see this blank canvas. To my right are all these visualizations options that I can use to create my report. I also have a list of all the fields and all the sources that are in my data model. If I expand Accounts, I see a list of all the fields. I expand Opportunities, and then I expand Sheet 1, which is all of my sales orders. As a matter of fact, I’m just going to rename Sheet 1 to Sales Orders. I’ll just double click it, and say Sales Orders.
Just know that for our Sales Orders order data, we don’t have account name here. Notice how we only have account number. If I want to create a report, and I want to see which of our accounts are ordering from us and what quantity, et cetera, how will I get that? Remember that we have a relationship between accounts and accounts in Dynamics 365, and the Sales Order Excel sheet. Because of that relationship, we can easily use the account name from Accounts in Dynamics 365, and then the order details from the Excel sheet.
What I’m going to do is I will just drag and drop Name for the account names from Dynamics 365. This will give me a list of all the accounts, and then I will say Order Amount from the Sales Orders Excel sheet, and drop it right next to it. Now you can see that what we’ve done is we have actually combined information from two different sources, and created one single view of it.
This is a table, or a list. I can choose one of these visualizations, so if I don’t like the table, I can just click one of these bar chart options, and now I have a chart right here. Similarly, I can say … Let me just copy this one real quick. What if I want a line chart? There is that option for me as well.
Using all these different visualization options, I can choose what kind of chart I want in my report. Now, let’s do something fun here. Which regions are bringing me more business? How do I find that? If I want a bird’s eye view of that, I can use the map control to create some sort of reporting insights. I will choose this Map control right here. Start with Blank for now, and notice how it needs some input from me here.
For Location, I will choose Postal Code. Drag it there. What’ll happen immediately, you’ll notice, is that it actually loads those zip codes on a map. Now I also want to see what kind of business we are doing here. For that I’m going to grab Order Amount from the Sales Order Excel sheet, and drag it into the Size field. What this means is that the bigger the circle, there more revenue we are getting from those zip codes. If I zoom in on this, you will notice that we’re doing a lot of business in Mexico. We’re doing a little bit of business in California, on the East Coast, but what’s going on on the rest of the West Coast, for example. This is something that’s hard to understand if you’re just looking at a list. But if you have a bird’s eye view of the map, then you get immediate insights as to how you are performing, and where you need to improve, et cetera.
Let’s say I want to look at how much we’ve shipped in the last six months, or four months, or the last one year, et cetera. I will add a slicer. I’m going to grab this Slicer control, right here, and I will base this slicer on Order Date. I’ll make this Relative. Now, what this gives me is a lot of filtering options. You’ll notice now that I can now start drilling down. I can now start streamlining my report and see how we performed in specific time periods. I can now say, “All right, show me all of our order sales revenue in the last,” let’s say, “six,” and, “months.” Now, immediately you will see a change in the report.
This is what is giving us the sales revenue information in the last six months. If I want to see what it was like in the last two months, I just come in here, and change that number, and it will reflect those changes immediately in my report.
Again, based on all these visualizations, all these controls, what Power BI is helping me do is, first of all, consolidate all the information from different sources. Then it’s giving me the ability to answer a lot of questions. Don’t just stop here. We have a lot of information that we share on our website through blogs, and webinars, and other sorts of resources. If you want to know how you can create Power BI reports for your organization, feel free to send me a message. Thank you for watching.