How to scale Microsoft Power BI with Xero

In 4 minutes I'll show you how you can build a Custom analytics solution for a Xero client and then scale it across your entire client base with Microsoft Power BI! Hello, everybody. My name is Cam from Etani. Today, I want to show you how quickly you can build something custom, and then scale it to a group of your client base. I have the old man online clothing data set here, and here is a pre-existing sales performance template. What I'm going to do is duplicate this template. I know that this client is a Xero client, I know they've got tracking categories, and I know they're interested in some variance analysis. I'm going to show you how we build something customer really quick, and then scale it to other clients.

Okay, so lets bring in tracking first. I'm just going to do a bit of housekeeping and delete that. I'm going to bring in tracking categories, as a field, so copy/paste is your best friend. Come down to your tracking categories table, find the option name, and drag it in, replace it with financial year. Now we have a slicer straightaway, so we can get ... This is quite a common one, is the tracking categories that use the divisional statewide for Australia. We just select them and change anything we want. The next thing, I do want this cumulative sales analysis, but I also would like to see all the tracking categories on the one graph, so I'm going to copy/paste. I'm going to drag that option name once again, but in this one as the legend. As you can see, it's picking up Queensland. We want to show them all, so we're just going to turn the interaction off between this filter. There you go. Now we've got all of the different states showing their performance across the financial year. Next up, I want to put a profit/loss here. I want to do a bit of variance analysis, the difference between the two, and I also want to show all of the expenditure as a percentage of total sales, because things like wages ... What is the percentage of total sales? It's something that you can track as a KPI so what do we do? Let's go and grab an existing template that I've already got in place, and we can put it in here. so this is a good template. It's just budget V actual, pre-built. We're going to bring that across, copy/paste. Okay, now instead of the budget, look at that, I'm actually going to get rid of it, because I want to do the percentage of total sales as a measure.

I'll show you how easy it is to write this type of stuff. We'll go with the measure of percentage of total sales of course, so let's write it out. That's your equation. Here's the equation. Percentage of total sales is your actual amount divided by sales, and remove the filter. We're going to drag in a new measure into our profit and loss. It just needs to be tidied up, ,make it a percentage. You can now move the goalpost. What do we get there? 11.3% of direct costs. Take off the first 2 quarters. You can slice this any way you want. Then updates for the first half, it was actually 12% of total sales. The flexibility here is endless. You want to compare to last month, two months ago, last year, last quarter. Anything you want to do, you can do. But now comes the fun bit. The scale bit, all right? But now we've designed this custom report, okay? We want to deliver it not just to this one client, but perhaps across a list of clients, okay? We can come to the query editor, go to data source settings, and here's all your clients. Here's all your data for all your different clients, so we're going to load a different client. This is just another one that I've got. This is the Xero demo company, okay, and I'm going to replace it. Then look at this. All this is the new data, 45 grand now, this is the Xero demo company coming out. All different tracking categories that they've got in there, file, save as, and you click the same report. Imagine the scalability of this if you were using SimPro, which is another data source we've got. The way you can move from client to client is just unbelievable.