 Hey everyone and welcome back to another video. In this tutorial, we're going to be looking at why you should be using the relationship and the data model in Excel also available to us when working with your tables and predominantly when you're trying to bring data from multiple tables into one pivot table. I haven't got an example to show you the problem I'm trying to solve, but in essence what it is is when we've got multiple sheets of information or tables or whatever format they're in and you're wanting to consolidate them into one single piece of information so that you're able to then provide a pivot table to obviously go through and analyze that information. As you're aware that the situations or the solutions to try and to solve that problem you would be trying to pull information all into one sheet maybe by using VLOOKUP or any other formula so that you had like one store of all the data so that you could pivot off that one central piece or source of data. What we're going to do in this is show you that actually you can retain keeping that information separate in separate tables and how you can then obviously use the relationship manager to obviously manage those relationships and identify those relationships between the table and then be able to treat them as one whole data set. So in order to do so we've got some information here so we've got a sales table what gives us simply just the country code the date and obviously the amount of the sales and it's probably obvious but we obviously the country code relates to a country in the country table that we'll get to in a second. So for each country we've had to see every month and I believe we went from July 2019 to June of 2020 so there's 12 months of data you'll see that there is again a month for each of those and an amount of sales achieved in that month. Similarly for budget we've got a similar very similar data set it's got the country code again the month or date and then you've got the amount what was budgeted for that country in that given month. And then lastly we've got the country table what gives us the country code and then obviously the country that aligns to that country code. So these are the three tables that we want to bring together and be able to just pivot into one single pivot table to allow obviously us or if in real world experience the end user to be able to filter through this data and the benefit obviously keeping it separate is as these two data tables or multiple data tables continue to grow or new information is added to them everything's going to flow through and refresh rather than having to obviously update any formulas and make sure things are pulling into new rows so on and so forth all that usual issues that we want to try and avoid. The one additional thing that we have to add to this and I was just going to add it now rather than prior to the video is in order for this to work we also need one other additional table we're going to need a date table and that table it simply just consists of this date table a distinct list of all just the dates that we are using here and it'll become more valuable or not valuable sorry it'll become more obvious why we need it as we move on but if you're used to working with databases especially relationship databases you'll understand why we need to have this separate date table for doing this. So what I'm going to do is just create another sheet and we'll call this one date and all I'm going to do is go and copy this column and just paste in column A so I've got the date there we'll put it into date format and all I'm going to do is go to date remove duplicates okay and yeah so it was July to June of 2019 or 2020 so you can see that's all the dates we've got available and just as a recap for anyone sit to convert that into a table all you do is click that table button yet my table does have headers because what the date header there click okay and then that's now converted into a table for us and lastly in the table design I'm just going to change this and call this date table just so we know what that one is so we've got the full piece of information now we've got the date table so we can obviously group everything by date what the sales were per country and dates what the budgeted amounts were per country and date and lastly obviously the mapping table for country to the country code so the first thing that we need to do is identify the relationships between each one of these tables so all we need to do for that is we go into our data tab and you'll see over the far right hand side or just off the middle we've got this relationships button here so it's going to sit here we're going to click our relationships and then you can see at the moment it starts as blank so we just need to go in and define those relationships so the first one we need to do is obviously we need to make sure there's a relationship between country and obviously the two budget and sales so we're going to go into here we'll go to table and we can find the country table and the column that we want to use to map is going to be the country code because country code is the unique reference between country and budget and sales so country country code related table and we can go into here and we'll get budget and we want to use the country code so we're going to do okay and then we're going to do another one so this time we're going to go between the country country code and this time we can do the sales so it's going to be the country code there and you can see as you build along what it looks like so you can see what our table is so we've got the budget country code and it's related to the country code within the country table and so forth with sales as well next one we need is we've got the date table so we now need to go into here and we need to go our date table and it's going to be just a date because anyone feel the name and then we can go into our budget table and select date and then we can go into our date table again and this time we want sales and you can see Excel has automatically recognised that it's obviously done a search and it realised that this is based on the naming it's obvious that we're going to probably want that date column so you'll notice that Excel will make a suggestion based on what it feels would be the actual column and you can see it's populated there in that example cool so we're going to enter there so let's just recap on what we've got here so we've got the budget is matched to the country budget is also matched to the data table our sales table is mapped to the country and our sales table is also mapped to the data table so I believe that is everything we have so the date against those and the country against those so I think we're all ready to go so once you're happy with your relationships all you now need to do is close out of here and now we're going to scope and proceed without inserting a pivot table so just to make this presentation purposes I'm going to go onto this essential Excel sheet here and I'm just going to go into insert a pivot table and as you're probably familiar with using you would even normally go into this top option here so you can say you're just going to select a table or range of data what we're actually going to do is go use an external data source choose connection if you then go into tables you can see this first option at the top here this workbook data model and it can see it's recognized that data model what we've built with those four tables if you haven't done the relationships obviously you wouldn't see this top part here you would just see these available tables here so once we've selected that we can go into open and where do we want this? it's obviously selected that we're going to go into C7 what's our last active cell in the sheet you can go OK and you can see it's just now thinking about it and yet there we go we've got a pivot table published for us so when we now look over the right hand side here we can see we've got our four separate tables and within each of those tables you can see the fields available to us so straight away we can see that it's worked everything is consolidated into this table and we can now proceed with populating up here at table as we require so the first thing we want is to take from our budget so I'm just going to take the amount from here and then we can go down here and we can pick country so we want the country from the country table as well that's the only place it's actually available and then the last one we can go into is sales and then we can go down to sales amount and actually we don't want country on there there's quite a lot of country so we don't actually need that there so we're just going to take country out and what I'm going to do is go into the data table to pull in our date so just to recap here I know we can see there's a date column in the budget and obviously also in the sales table but we need to ensure that we're pulling date from the date table because this is our central point for date and then we're going to use this as a starting point and then the other fields will map against this so to avoid any problems we just need to make sure we get date from our date table and we can see that everything has now mapped into here for us one thing we just need to do actually just before we get confused so the first one I pulled in here was budget so I'm just going to go field settings and we're going to go into put budget and I'm going to just change the number format to a number okay okay and then the second one was then our sales let me change that to a number as well okay so there we are we've now got a pivot table that has summarized our information and we can see the benefit of this is the tables are still separate so they're separate entities that we can continue to update with more information but they're ultimately going to get pulled through to this single table here the other benefit we have with this as well is that we can also insert a slicer so let's go insert and where it's gone over here slicer and let's go to the country tab so this is where I jumped ahead really by pulling country into the pivot table initially go to the country sheet and we're going to select country and you can see we now got this slicer available to us and let's go down to somewhere like the United Kingdom if it is even there no it's not there I don't know where it is let's pick another random country I don't know what the number is going to show let's go United States of America so there we can now see the benefit of being this slicer in is we're able to now filter the table and see the different amounts based on each country and you go to UAE as well Uruguay any other one available to us and it's now got to all centrally stored into that pivot table so this right here is basically the main reason of why you start using relationships when working with data especially in multiple tables or multiple pieces of information that you're working with one other bit we can do just to go a bit further with this okay so just to improve this and go one step further we can actually add a measure what will allow us to work better with this budget and sales amount all we need to do is we're going to go into our pivot table and the first one is budget so I'm just going to right click on to budget and you can see I'm just done that on the right hand side here hopefully you can see that and I'm just going to go to add measure so you can see it's in the budget sheet so what's my measure name going to be well actually I'm going to call this the budget and the formula for this is going to be the sum and it's going to be where has it gone budget amount close that okay and you can now see we've actually got this measure in here what's going to be the budget amount and all it is is just the sum of the amount and this works really well just because obviously it defines it as the actual value what we're looking for but also it's going to work when we come to want to do other measures like changing the variance maybe the difference between the budget and the sales amount so I'm just also going to come down to sales and do the same down here so I've got sales measure name is sales and the formula for this is going to be equal sum and we want sum of sales amount close our brackets there go enter and we can see we've got that value and now available to us here as well so let's just actually just go into edit measure let's just make sure it is a number so we want to enter number format and also if we go into budget we can go edit measure into number so you can do this when we first set it up I just forgot to do it now I'm just going back and doing it now and then if we remove these two that we had here what we can do is now just drag in budget and also bring drag in the sales you can see how it works the same but we can now identify these actually as what they are the budget and the sales the one last thing that I'm going to add into this is another key piece of information could be to see how does our actual sales amount fair against the amount we're budgeted for so what I'm going to do here is just go into our sales table and do add a new measure and this time we're going to call this their percentage and basically all that stands for is the variance and now this is going to come into our formula here so this time I'm going to do is divide open brackets so our numerator we're going to do a calculation here because we want to find initially what the difference is so for us because we've now got those so because we've now got those measures available to us all we need to do is reference those so if I start typing sales you can see we have this sales measure here so double click that so we want to do that minus the budget and you can see when I start typing that you can see obviously we've got the budget budget budget and the budget some of them out so these are all everything we're able to budget but because we've got that measure we're just going to use that one there just makes it a lot tidier on the page as well so the next thing we're going to do once we've done that is put in the denominator and the denominator for us is going to be that budget amount again and then alternate result so if there is an error and for whatever reason we just want to show blank so for us we're going to just go into one more I'll just click that a bit so just do comma and I'm just going to put in here blank like that like so and then that is the entirety of our formula so what it's going to do it's going to take our sales amount and the minus of our budget so it will tell us obviously what our amount increased or decreased is it will then take that increased decrease amount divide against the budget to then give us a percentage so what the variance percentage is and lastly all we need to do for that is we'll just change this to our number we'll put it to percentage one decimal place and select ok and we've now got that percentage here so let's just drag that into our table as well and we can now see what our percentage variance is so for that first one you can see we've got the 2196 and then the 2204 and obviously that's been a percentage increase of 0.4% and actually looking down that page there there's not been really a great deal of change happened at all and I don't know what's happened to my slicer so I'm just going to enter one more time so we go to slicer and we want the country here and obviously the benefit of having done those measures and also the additional variance percentage measure as well is when we go to set any one of our countries we can see that we're going to get that information automatically populated for us as well so this is a really good technique to be using not only does it simplify working with multiple tables of data it also just gives you that streamlined way of working with it allows continuous update to the data as well and also gives this professional presentation ability when it comes to like going through with a colleague into this information back in live time so I hope you enjoyed that video if you did please do give it a like it's not only greatly appreciated by myself but helps that all important YouTube algorithm if it's the first time finding the channel or you're a peer viewer please do subscribe to the channel hit that bell notification button and that way you'll be notified of all of our future videos before you go don't forget to check out the other videos on our channel you'll see everything from other functions and formulas through to tips and tricks we've also created some playlists so you can see these categories together so make sure you check those out and get all those useful information and obviously as always don't forget to subscribe and hit that bell notification button