151 - Power BI Item Budgets in Microsoft D365 Business Central

Power BI Item Budgets in Microsoft D365 Business Central

Business central as you know, it connects to everything in the cloud, its new, it does everything NAV does, you can modify it. So, we really like it. One thing that we got to think about when we are working with Business Central it's not just the ERP system, is the entire cloud that we are working with right now. The whole Azure stack, Office 365, everything. That’s why I'm kind of doing a lot of this boundary things because we have to start thinking more out of the box. Out of NAV so to speak.

What I'm going to do know, is keep going on Power BI, with Business Central. Down here we have some Power BI reports, and I know we activated that in one of the videos and all that. But I always wanted to put this report down here and do it easily, we'd like to see actual sales vs budgeted sales. Like how much we sold compared to what we wanted to sell. In NAV we have something called Sales Budgets.

Sales Budgets
I know it has to do with sales, but, the best thing about sales budget is that it has to do with the actual inventory, like sales of inventory. So, you can put in the budget and quantity, what amount you were thinking about getting for it. Also, what cost you have. As you can see here, I've put in two amounts for the Athens Desk and the Paris guest chair and, just to give some examples.

The idea that I have is I want to extract this data into a Power BI report, also extract the actual data that we have and show that side by side. I think Power BI is the way to show it. You know we can do a lot of things with it in Power BI, obviously, you could probably see here in the report, its more static, not as much fun. So, let's go with Power BI.

If I go and drill into this number here, I get something called Item budget entries. Here you can see sort of the history of what I've been entering in I actually put this as 49, 5 and then I changed into 50, so you add these up together, you get 50. In any case from like a database management standpoint. If you sum up the sales amount here for this item for this date range. That's the budget. Sort of a little technical but I hope you get it. we close this out and the reason why I'm talking about that is because we want to expose the page that I just rolled into to Power BI. How do we do that?

We go into web services and I hope you are not scared of this table we services, it’s a really simple table, the should really just hide these strings here. Whit business central you really don’t need them. I can show them if you need them, but you don’t need them. They look scary.

If you go in here and I'm going to scroll up a little bit, I have exposed the item budget entries, this is what I want to drag or the data in here, I want to drag it into power BI. I call it IBE and I published it its right here, and it creates this string here that I have nothing to do with because I'm going to connect straight to the cloud with my username and that's it.

We also have the item card exposed, that was exposed before. And I have Item ledger entries exposed, so those are the three tables that we need. I'm going to jump into Power BI. I can just go-ahead and say get data, I can type in business central and then click on this and log in and I get my tables, I've already done this because this does a little bit of a wait time when it's getting all the tables, so I'll skip that. In here I have the tables, I have the item card, the item ledger entries and item budget entire. I've gotten all of that into my Power BI. Why do I need the item card?

I know why I need the item budget entries because that gives me all the estimated sales or budget sales, I know we need the item ledger entries because those have the actual sales, why do I need the item card? the reason you need that is because you need to connect these two. You can't connect those two together, because that would be a many to many relationships which is messy. You must connect both to item card. I' m going to show you how to do that.

Connect item budget entries and ledger entries to item card.
You just go to this-this screen over here, that manages relationships and click on the button that says manage relationships and hit new and in here, you can select the item card number and then here the item budget entries and the item number and hit ok. Now we've connected the item card to the item budget entries. Now I also what to connect the item card to the item ledger entries. Now I hit close and I've connected the two together. So, the item card is connected to both, the item budget entries and the item ledger entries.

What does this give us? this gives us the ability to actually go here into our presentation view or visualization view and start picking things. I can go here into the item card and I want to show the number because we think the number is important and I want to see the description. Everybody should be able to do this, this is easy, but how about this? I want the sales amount. This is the budgeted amount alright? And then I want the actual amount so, I can just go in here and get the actual amount.

Since I connected it already knows, so we are like under budget on the Paris guest chair and way above budget on the Athens desk, this a good seller. Now I want to just throw this on to Business central, we want to maybe do some formatting to this because one of the things that happens is when you actually move this into Business Central is that the fields become really small. You are going to see in a bit why I'm doing this, but you can actually format this any way you want. I could change the name over here if I wanted to.

I can do many things, but I'll be wasting your time. But I'm not going to do that, I'm going to publish. Let me go ahead and save this first as test 3 so you can see I've been doing some tests. I publish into my work space, that's fine. And now It's taking this information and throwing it back to the cloud and it's going to be on my power BI work space. That is pretty quick so, let's go ahead into Power BI and see if we got Test 3. It's there, now I'm in the cloud and I see my little screen here, this is very good, promising.

Let's go ahead and see if it actually came into the power BI screen. Yep! Look at that, test 3. I enable that, hit ok and I get the screen. Now you maybe get why I make this so big on the other side because this is actually quite small here but it's readable. So, you have to kind of work with the sizes of the things. This is how you actually can do actually to estimated sales, which is really cool, but I don’t know if you notice that it, but there is no time frame in here, of course, the item budget could have had some time frame installed so will be filtering on that, but maybe for the next video we'll be figuring out.