Advanced Analytics Techniques For Power BI

welcome welcome into uh today's session we'll just wait a moment while everyone is jumping on board i think i've got something quite interesting for us today i was just um reviewing it just before i started the call so yeah looking forward to uh jumping into it we're gonna i'm gonna do some quite advanced things today i think so um i'm just gonna just do a bit of setup here while we're waiting for everyone to jump in let me know um we're calling in from on the chat i'm really keen to um a lot i absolutely am really loving these sessions lately i'm really enjoying just walking through a whole range of different developments and um different techniques that you can use inside of power bi across all of the different um key pillars to power bi development so i want to do something similar today and i think i've got a good data set to that's advanced enough um but we can do a lot with it um so and we can actually take our analysis and lots of different directions so i'm just just organizing myself here just give me a second let me know um are there things that you want to learn today are there some um things that you've been working on that would be interesting covering in in some way um let me know and let us all know where you're calling in from um and also just like we've done in all the recent sessions i'll be giving out a free membership to one uh one of those one of you on the call today all that's required is you just need to comment um be engaged in the session uh and also subscribe to our channel and that's all that's all you got to do so um well worth um well worth it just uh you know you learn a lot from being engaged in what we're talking about but also a chance to win a free membership by the way today is our last day we're having um a our best sale ever around around membership so definitely check out our home page our website if you want to learn more about that um i think we've got 40 off at the moment so uh definitely check that out let's see it okay okay i'm just going to share my screen and i've just been doing a bit of organizing here now the um the idea here is to do some advanced analytics right so this could go in a few different ways now on our website we actually have a few courses which cover a range of different advanced analytics techniques right and so you know i'm i'm talking today well we're going to build up to these but we hopefully will be able to get into some of these sort of topics that we've got time normally detection cluster analysis outlier detection pattern recognition customer segmentation parader rules secondary table these are the sort of things that i'd love to be able to cover in some way today but we'll i guess we'll just see how we how we go in this session because we've obviously got to build up our model et cetera to get to this point um but but yeah this is that's my goal um you know there's also lots of other techniques we can use um particularly dax formula patterns and so i'm going to go into some of those as well now what i did is i i did a quick look around for some data and i think i know which data we're going to use today so we won't spend any time sort of figuring out which which data we want to use the uh the data that i selected was from the kaggle website okay so there's this there's this data set which is i'm actually one of the most popular data sets just for analysis on the kaggle website and it's uh for a company um called o-list which i'm i'm not actually quite sure what o-list is maybe it's like the amazon of brazil um if any of you know just just let me know i haven't i haven't um i haven't used it that that well i i haven't been aware of it oh no it's okay oh list nigeria interesting so maybe it's actually african but it says it's brazilian some look maybe maybe it's a global company i don't know um but uh the data set that we have available to us is like a full data set for an online retailer right and so there's a cust there's a customer data set um location data set orders payments reviews um products sellers category right so it's like a full-blown um data set on a on an entire company so it gives us a lot of scope to do a variety of different things and so that's why i quite liked it because then we can take this in many different directions with with our analysis okay so what i'm going to do just to just to really speed things up i'm just going to bring the data in we're going to start i want to go through quite quickly i want to show you just like i have been doing in recent times the four key pillars to power bi development right so that's what you're going to get the most out of you're going to get the most out of these types of sessions is if i show you my process my framework and then you know i'm getting a lot of feedback that a lot of you actually using that framework yourselves which is which is fantastic and exactly what um my my goals are like my my big goal for these types of sessions so just before we begin um you know based on based on the the the details of what we've gone through uh of this data set right is there anything uh that is on your minds that we could um you know we could review so think about some pieces of analysis that we could do i mean there's plenty that comes to my mind you know there's things like we could understand who are our best sellers right where are most of our sales coming from which products are the most popular uh which customer segments are the most popular which regions are the most profitable so on and so forth so there's plenty of great analysis that just comes to mind very very quickly for me some of these some of these tables are actually quite big as well so we'll have to sort of contend with that has anyone got any any you know issues with going through this one or are they desperate to go through something else um just let me know in the chat but i'm gonna start anyway um because i think this is gonna be a good one so let's just let's just get into it and i'm going to i think all of these are csvs yeah all of these are csvs so we're going to bring in a whole range of these i could actually probably bring in the whole folder so let's let's give that a go i'm going to do just a little bit of setup here and it might yeah this is a little bit which day of the week has max sales yeah perfect that's a good one and it's actually very easy to do um but uh we can um yeah we can we can do lots of interesting things around that okay so i'm going to actually bring in an entire folder here because that will speed things up a bit i think i think it is v2c this one so this is a nice little technique if you've got a lot of files is um instead of bringing in every uh file um we can bring them in as one folder combine and transform data transform data um forecasting sales yep i agree that's a good one this isn't isn't working as i imagined it would be so let's just start again maybe i do need to bring them individually which will be news to me cross-selling yeah that's a good one from where's yeah yeah so these are all great so let's let's just uh just want to get us to a point where i can do this quite quickly so give me a second i thought i could bring in with the folder but maybe i can't um but maybe i can't i just instead of waiting around to find out let's just just quickly do it this way now i'm going to lift it leave out the geolocation data set um to begin with here because it's quite it's quite big and i just want to um just have a quick look so you see this one here is quite big and so i just want to get these smaller ones in first and then just see how big my model is and then we can um yeah then we can do something else after so this one here this is a good good one from jimmy um yeah so definitely definitely we're gonna do that we're to do that there's so much we can do here i think we're going to be um uh yeah we're going to be sort of really working over time to know to prioritize you know to prioritize the things that we want to have a look at okay reviews and got orders now i'm gonna i'm gonna i'm gonna i wanna work through a lot of my um like clean ups here quite quickly um and i might i might even overlook some of the some of the things that i traditionally would do just to speed up but we'll see we'll see how we go i'm happy to go for look as long as possible here i just i just really enjoy going going through these sessions so working through working through analysis so we'll just um we'll just keep going until um and so we all all get a bit uh or we'll get over it but um there's plenty we can do here so okay i might actually while some whilst i'm sort of on a bit of a mission here i'm gonna bring in that geolocation we can always take it out we can always take it out if it's no good it's probably because it's sort of like locating every place in brazil that's probably why okay so the first thing i would do here right um is i'm going to just i'm going to clean up all of these because we don't want these these these unproper names okay we want to we want to make it as all intuitive as possible so order items order payments reviews maybe that reviews isn't as important but let's see orders products you see here just even by doing this it improves the way that these sort of tables look and fit together get [Music] so a couple of things that i identified quite quickly like these titles need to be in the need to be in the header so i can go transform use first rows header so that's one thing that we can do there let's just quickly run through some of these a lot of these names aren't aren't up to scratch right and one of the other things that isn't great is this id column one of the interesting things about columns inside of power bi like really long columns with massive ids like this actually take up a lot of room in your model and so if you can get a convention on your ids that are a lot shorter that's what you should go for so this might be something we we have to clean up but um we'll also i guess we'll see you see that the product idea is the same like this is going to take up a lot of room in our model it's going to really blow out the size okay so we've got times here as well so this is when orders actually happened or this is when they were delivered okay so order review okay i don't know i don't know if we actually need the sort of review so i'm gonna i'm not gonna load it in um i'm gonna call this like i'm gonna create a new group here and call this supporting tables so this is this is just a nice way to just get organized really quickly right as to um is to group things is to group things um so that you know what is going to go into your data model and what isn't so in this particular case i'm gonna i'm to select all of these i'm going to move it into a group i always create this group called the data model so that i know what is going to go into my model okay now one of the first things i try to identify here and i explained this like quite a number of times is i'm trying to build up the model in my mind before i actually get to that point right so what what i what are going to be my lookup tables and what it could be my fact tables and sometimes what i what i do is i actually in a more complex model as i do this i break out um the tables which i know the city these and my um these in my mind are going to be my look up tables right and these are going to be my fact tables so sometimes what i do is i come in here and i actually create a group a new group i call this lookup tables like so okay and then this actually removes the need for um data model here i can change the name of this to fact tables data mode like that and this is just total this is all just for organization it's all for continuity of your models etc um yeah just makes makes a big difference and so i'll then also maybe look to change this up put that above there maybe bring it down here so these are just like little things that i like i like to do but you know all of a sudden it makes a lot more sense right now one of the other things that i'm definitely going to need here um and i know this straight away is my date table okay so i'm just going to the analyst tab here this is this is our own application that we've built and i'm going to go and grab my day table and so this is the quickest and best way to build you know a date table that has everything you need right and so this is a table from melissa one of our experts and it's name of extended in it so this one here this one here from melissa this is the best date table um you can find it's it's catalogued in here i just copy the code and then i come back into my model here okay and then this is going to be a lookup table so i just go blank query so this is something i've i've showcased many many times and then i go okay and then i'll just put in some random dates here and then we'll fix it up later once we once we know um what so i'm just importing my start date end date um financial year start month week we can change these around later on and work day start months per start number okay so now i've got a date table i'm going to call this dates and then i'm going to bring it up here okay this is the dates query okay and then i'm going to move this to another group so once you um once you do this a few times right i'm going to parameter query this is something that is just can become automatic automatic and so you know i i can work through this pretty quickly now just by yeah just just almost by default okay so let's just again quickly look through here and see if we can think think in our minds how the model's gonna be created okay so we've got customers here and so we've got a customer id so this is probably going to we're gonna find in these tables here a customer id column and all of these right um uh that's the items so order payment okay so it looks like order id is going to be our sort of intermediary table so it looks like customers would flow to orders and then order payment is going to be on a layer below that because the orders the orders has an order id which is i presume going to be unique and then our customers is going to be linked up via the look up table okay cool so this also the important thing is is in my mind is my waterfall technique the waterfall technique around um how your filters are going to flow that's how i'm thinking and i want you to think in a similar sort of way like if you don't have a framework around how your model is going to look this can get very complicated very fast okay now um these these sort of columns inside of power bi are not helpful as well so we want to we want to probably just look at it by by date right we're not too worried about um i mean we could we could we could do something between when it was ordered and when it was delivered so let's let's try to simplify that down for us so what i would do time like specific times uh um in this case because this is over a longer period of time like let's not get too worried about the times right so what i would do is i would go to transform here and i'm just going to get i'm just going to extract the date out of here okay date only okay i'm not going to be too worried about this column as well so i'm going to remove it and then i'm going to go date only for this column as well because that's going to give us the um delivered date for carrier and this is the delivered date for customer okay so we could do some interesting and we could do some interesting analysis i'm just based on that right estimated delivery date okay let's just get rid of that so i'm just gonna i'm really just trying to simplify this down um purchase water purchased water delivered um carrier and then order okay so there's a little there is actually a little bit what we need to do here isn't that so but um but this is all um one once we simplify this all down um it's going to um it's going to make it um a lot easier so jimmy's got a good good question what differentiates that date table to say the standard calendar auto um a lot so if you check out this this date table gives you every type of date column or date dimension that you could ever want like it's incredibly detailed and anything that you want to do with dates you always want you need the most you need a comprehensive date table here okay you have to have a good date table and then everything around time can work off that day table okay so let's just clean up a few of these other ones let's have a look so order items shipping date price freight value so order payments so this is what is going to um okay so we've got payment type we can come back and clean up some of these a little bit late so later installments and i might do that just to value okay yep so this is a good one we can definitely do this like to see sales by products see which items are trending up and which are trending downward drill downs for regions per country yeah so we can definitely like 100 we can do all of these things um and i just need to do a little bit more cleaning to be able to make sure we can do it so we've got latitude here we've got longitude so one of the i'm going to move on now but one of the things that i would honestly do is i would go through and change all of these okay i would um i would i would go through and change all of these column names into proper format like this okay you don't want you don't want any of these underscores lowercase everything you want to be proper because these columns go into your measures they go into your visualizations they go into your chat like your axes of your charts and you want them to all um populate in the correct format so that you don't have additional work to do okay cool okay so i'm going to i'm going to close and apply um and let's see what this looks like we'll try build up our model there is plenty of other cleanups that i would do in the query editor but i just want to move on and we can we can clean it up later on if um if we want to but what do you what do you think about my my um that that sort of process that's something you're doing now is is there is there some elements of of what i did that and can improve what you're doing i definitely want you to all realize the potential in the query i mean there's just so much great stuff that you can do there right so hopefully this doesn't take too long my my fear is at that data set is quite big but we'll see okay here we go this looks like we've got some um some movement which is good okay so the first thing we need to do um the data link is oh sure i will share with you i'll show you here by the way is the it's going to the data so i just got it from the kegel wig so grouping of queries is wonderful concept yeah so please please do that grouping it makes a big difference okay so it looks like power bi has um tried to it has actually tried to guess some of my relationships which is fine okay and what i need to do now is get this into a format that i understand okay so lookup tables for me always at the top okay so we want to we want to quickly get our lookup tables at the top and no matter what data set i'm using this is always the way that i look to um i look to organize it right in this case we've got something a little bit unique where i think we're going to have an additional layer within our model but that's absolutely fine okay so i'm just going to continue to get these a bit bit more organized this category name might actually be above here you know one of the things that i just realized we could do is this particular table here we could honestly probably merge it into into here do we even need it because we've got product category we don't need it we don't even need it so that's another one i'm going to quickly just remove out of here so i jump back and forth into the query editor all the time if i can if i can make some improvements to my model i always try and do them at the query editor level because the reality is is that we don't want to we want to simplify our model as much as possible and if i go disable the load here right i mean i could i could totally delete this if i wanted to but if i just disable the load i can keep it in here for reference bring it down to my supporting tables and then again i'll just sort of organize things and clean things up a little bit better here okay close and apply because that category name was already there so i didn't really even need it which is good okay so let's get back to here and start um organizing this now i always say that i don't really care what is in the tables at this point i'm trying to just get it organized so that i can visualize this in my mind at all times how this is actually working okay i'm always just thinking okay how can i simplify this area how can i make it super intuitive okay so already this looks more intuitive right it looks simpler just by the way that i've structured it um there's still a little bit for me to go i think see i think that we're probably going to have to do something a little bit unique here just on the way that the data is set up we could probably improve this but my feeling is that we need this sort of intermediary table because what happens is a customer orders right then that order i'm going to think about how the relationships are going to flow here someone orders but then that order is what drives what items are in the order and then when the payment was made via the order id right and then the other information like um products i mean honestly even this could this could be a lookup table but my only concern here is that the customer information is um we never want to we never want to have relationships between lookup tables or between fact tables right and so you want to think okay the relationship's going to flow through through this down to my order id and then it's going to flow down down the waterfall okay that's the way i think of it so this i've got quite a unique methodology here but i i believe that this is what is required for power for modeling for modeling and power bi i believe personally that the old theories around schemers and and the complexity around that is too much and so i just try and simplify it down my concept is a waterfall technique have your look up tables at the top have your filters flowing down the waterfall at all times and never across okay never across or between the different layers that you place on your model okay so it's a little bit unique but um you know as you work through many examples over many different data sets you'll find that that is the best way to learn how this area works but also implement it across many different examples okay so um i think this is good enough for now there might be some updates we could make the only thing that i'm thinking right is if we go to the orders table like could could this orders table actually be a lookup table and we could we could maybe create it as a lookup table because we could integrate these two tables together we could do that in that we could do that in the query editor potentially so i'm just going to have a quick look at the data and here okay so let's have let's have a look at our customers table here so customer id right well there's not actually that much information about the customer so there's potentially we could um geo locations see this this is a lookup table which could which basically links up to here right um and if i come to orders customer id i could i could honestly bring these two tables together i'm pretty sure they're the same amount of rows okay so yeah that's gonna that's actually gonna simplify it even more for us so i'm gonna do that i'm going to do that let's just have a quick look at this so these are again this is just the things that are trying to work out so these tables i just realized are exactly the same length and i was able to identify this as well because if you look at this power bi picked up a one-to-one relationship so that means that there is only one um they're the same amount of rows basically there's there's every customer is only referenced once in here and so in in theory you know in theory you would think a customer would order many different times right but it looks like in this data set it's only once so maybe this is a this this could easily be a lookup table okay so what i'm going to do here is a little bit of a trick i'm going to i'm going to click on the orders i'm going to click on this orders query here right then i'm going to go merge queries i'm going to merge by customer id the from the customers table customer id here okay now this should give me this correct result here the selection matches okay and then i can go okay okay and then i come up here and then i just select the columns which i want okay so i probably don't need this column um and i'll just get i'll bring all those ones in okay i i actually probably don't even need i don't even need that column either because that that customer id is this one here yep okay so i'm actually going to get rid of this one as well what did i do okay okay so now that i've now that i've merged these right i can then again disable this load right and i've simplified my model once again so i don't know it's just running running a bit slow any any thoughts any questions on what i'm actually trying to do here um and i'm going to bring this down here queries like so okay okay simplified it now i'm going to go close and apply and i've also by doing this i'm taking data out of my model which is almost duplicated right like that id column had 94 000 rows of that really complex like really long column with lots of letters and numbers and i've just taken a whole lot of that out so my model is going to be more efficient it's going to be smaller just just by a function of doing that and also my my just general model is going to make a lot more sense here so i could actually move this up to um yeah so this is this is a really really good point here spending time in the data model will make decks simple i am tr like literally my goal is to always try and make decks as simple as possible like that is that is my one of my my my like core goals with any work i do in here um and it's something that i wish was more widely um followed like more more users of power bi follow that because i'm so many times your your formulas are complex mainly because your your data is is not optimized that is the reality so um you know if you follow this framework that i'm going through you'll be able to you'll be surprised so surprised at how quickly you can create some really interesting insights okay so i just realized like this probably is not going to be a lookup table but i can still simplify it down right um i'm going to because i just realized like the date is going to have to link to this particular table and some of these are going to have to uh um link to these that specific table okay so let's just go through some of the other things here now i only have one date table in here so what i'm going to do is this okay this brings a little bit of additional complexity to our model but that's okay remember you can only have one active relationship but that is absolutely fine in this case we're going to have some inactive relationships to simplify the model we still want to just keep um one date table that's all we need and we can we can turn on these relationships with formula eventually okay but i just want to maintain the simplicity here and potentially you know just to you know i'm all about okay how can i maybe maybe these here can go like this and then what we have um when we brought the customers in right we want it we this is what's going to link up to the geolocation table so maybe geo locations can just slot here right and then we link it up through that a couple of things i want to do just realize i'm just going to quickly go back here i know this is taking probably this is taking a little bit longer than i anticipated but this is all great stuff and and i'm willing just to just to keep going for um quite a lot of time quite a long time so we can keep working on the analysis but you know even even though we're not going over dax formulas so much right now you know i hope i hope i'm making you aware of just how important this part is is to actually make your tax formulas work to make them seamlessly calculate and um be able to use dax patterns etc etc okay um so i'm just going to go in here now i should clean these up a bit christmas city customer state okay let's just we have a city in here yes okay we have a location city ocean state now interesting interesting right location city location state we don't really actually need these columns if you think about it we don't need these columns right well no we do we do want them in here but we don't actually need them in here because we can link all these up by this one code okay so what we can do is we can go we can call this like zip code area right okay i can i can actually get rid of these two columns from in here i don't need these actually now that i realize this i can remove these columns from my query because i can link up this i can get all the information i need about this particular region from my locations table here okay so i can call this a zip code right and then i've got i've got the latitude longitude location city and state all here already okay so that's another um another good one so so this is this is a good point so yep right thought process habits early on yep it makes such a big difference like it's just crazy how big a difference it makes to your overall development and so this this this from jose i i don't agree i don't agree your column names you want to be in proper format you don't want any underscores you know i i mentioned this at the start um you want them in proper format so i just don't agree with this in power bi because within power bi your titles your axes your your names and your measures they all are derived from column names um and so you need to get them in a proper format so that all of that stuff pre-populates why did i use inactive relationships here because i want to simplify my model if i wanted to analyze by these different dates right i don't want to create multiple different date tables i want to have one date table and then i can turn on these relationships very easily with measures within my measures okay that's why okay so now i've got my locations i've simplified this table as well remember so i got rid of all the the location information and i can link up this zip code to the zip code area so it looks like it looks like i need to in this location area i need to get rid of duplicates potentially so let's just give that a go and it could be i mean maybe there's errors or something like that so let's just remove remove duplicates maybe your lookup tables need to have a column of unique values and i'm going to also at the same time um let's just have a quick look here okay maybe that will do it let's just let's just go like so okay great okay cool i got my one to my my one-to-many relationship i can collapse that now okay so let's just see was it really in the sellers uh-huh we had it we in the cellar we had a zip code as well so this can be dragged down here too hopefully this will give us a one to many it's giving us a an inactive relationship why is that let's have a look to see why we don't want we shouldn't have one so location and orders table hmm that shouldn't be the case but let's uh i can't see why because you the all of my filters should flow um all my filters should flow down the hill okay we'll figure this out later figure this out later so so i understand you know if you come from a sql background yes but we're not working in sql here we're working in power bi and power bi is a visualization tool off the back of um off the back of the model right and so your visualizations all are derived from the information you have in columns et cetera so that's why it takes a different thought process a different a different way of doing things and that's why i personally have worked it worked out a unique way of doing things here and and i like to sort of showcase my thought process around how i do that and this is also why i think differently about the model here because i feel like you should this is a this is a this is not a cor this is not a you know it looks like a database right but it's something different it's something different because it is a visualization tool as well and you're bringing in we're bringing in analysts from an excel background so i think you've got to approach it a slightly different way what i'm going to do here i'm actually going to delete this for now we can maybe link that up at a later point let's see okay so i think that we are pretty close to i mean there's plenty of like cleanups that i would do like you know all of these names are just horrific right so i would change all of these names and make them a better format but we won't we won't do that we might do it later we've got time okay so let's let's start creating some formulas okay um yeah okay so let's let's let's jump in here so the first table i'm going to create is just my measures group okay so i do i do this very quickly at the start of anything i do just because i'm going to start creating some some some simple measures and now that we've created our model and it's relatively intuitive right this is where we need to quickly come in and start creating our core measures okay so i always think okay what are the simplest measures what are the simplest measures that i can create at this point in time right that i can can start sort of analyzing the data okay now to me if i just have a look down here the simplest measures i can create are probably going to be they're going to be in my fact tables right so i've got my order items so we've got um we've got price and we've got freight value so this is i guess this is sort of saying like how many i guess is what is this saying this is sort of saying um this is giving us the is this the price of the good or is this the price of the freight okay so there's a lot of different values here okay so maybe this is the price of the item i don't know probably um okay so order items or the payment payment value okay so we're probably just going to sum up we're going to sum up this we're going to sum up this column and we can do a count row of this column the orders right this is this is every individual order okay so let's just do those so i'm just this is all i'm thinking at the moment it's like okay what are my simplest measures my assemblage measures are going to be total sales and total orders okay so let's let's create those and i just do these by default i don't get too complicated in here i just go total sales sum of the payments value right just quickly create these and then i can go total orders we can also do total quantity as well total orders um and this one and these these are where i call upon just all my simple like aggregating measures and iterating measures so like sum sum x average count rows so we'll count rows here orders okay and a lot of these you know a lot of these columns as well we're going to we're going to hide these columns as well this is a this is a nice little trick i'm going to show you in a second because a lot of these columns are just just not not very helpful in terms of you know we they're important in terms of the model but they're not that important in terms of our measures our calculations our filters etc are slices that we're going to put in our visualization okay so you see now that i've got my i've got all my measures and i measure in the measure table okay now um this is what i'm going to i'm going to do this here okay so these columns here aren't any use to me now like as i just mentioned okay so what i like to do is i always like to come through and as i do this is i'm working a hide and report view okay because they're just not useful at all we want we want our key dimensions to be the ones that we put in our visualizations and also measures measures are the um the what are going to showcase our results inside of our visualizations we're not going to use any of these columns in our visualizations they're going to be in measures okay so it's just a bit slow a couple of um points here so power bi works with other data model types single table snowflake mod but it works best and optimized for a star schema model yep that's right one of the things i have not done is i have not saved my model so this is uh starting to concern me that is taking a long time to update i'm sure a lot of you have found similar sort of things okay so i am going to save my model so i don't lose any of my work any anyone got any other thoughts what have i um what what do you like what what what do you think i could improve so i'm just going through i'm i'm just hiding hiding these columns right they're still going to be in my table i just don't want to see them because they're not helpful and the shipping limited date i don't need that either okay order payments same sort of thing let's just go through and hide these okay orders hi do all of you actually do this do you go through and you hide these things i think it's a a really important tip and also even this thing like zip code right i don't need this i don't need to see this because my location table has that zip code so i don't need to see that and the other interesting thing is i don't actually need to see any of these as well because i'm not going to bring these into any table i'm always going to derive my date by the date column in the date table and so the reality is i can actually i can actually hide these too it just again just simplifies down um just simplifies down what i'm looking at so i'm just going to go hide and it really targets my my mind onto the information which is going to matter okay okay so let's just come through to some of these yep so you can you can actually hide them in a few different places that's fine so i'm just going to come through here hide so this is quite interesting so there's a lot of information about the actual um like the actual product and this would be interesting for shipping right this would be really interesting for shipping okay so let's have a quick look let's have a quick look at some information we can already um we can already create right so we've got product category name so these are all the product categories that we have right and because of the way we've set up our model we should already calculate very quickly for us well that hasn't worked is it so let's have a look why so products is not hitting order payment sending order items though this is interesting this is quite interesting actually um so somehow somehow we need to actually link up these two tables which is insane can i go like this it's meaning too many mm-hmm this is a little bit tricky that's a little bit tricky so let's have a think that's everything there is a way there'll be a way to solve this so somehow there's no relationship like i'm getting a weird result because there's no relationship between this table and this table where i'm calculating up the payment value that's why i'm getting all these results here which which don't give me the result i'm after so we need to solve we need to solve somehow how how we can jump across here now i think we can we can quite easily do this um using using using using a relationship called treat as i think no we we probably want to merge these columns i reckon i think this is what we want to do we want to solve this in the model so we want to instead of instead of not in the model we want to solve this in the query editor so i think we've got orders we want to try and merge these two particular columns these two columns here based on the order id that's what we want to do that's what we want to do okay so let's let's let's see if we can do this so let's let's merge i got order items here and i'm going to merge this with my order payments so instead of yeah this i think should work right so we're going to based on order id like so okay so there's a little bit of a mismatch but looks pretty close so it's probably good enough for now if we have a look at these and i'm going to merge it okay then i can get i don't need the order id and i'll bring all these other ones in okay cool so now we actually have all of the details in one table by the looks of it okay this is much better this is much better okay then i'm going to come here i'm going to disable the load again i mean maybe even we could like we could even merge this table in as well that would honestly make it even easier wouldn't it well let's let's try and do that let's let's do this as well just go merge queries this is again query created the query editor is just incredible um what does this look like okay this looks pretty good as well um so i'm just going to merge this so again again i mean we can make this so much simpler for ourselves can we i should have done this up front but sometimes you just don't realize these things until later on and so the order id i can just get rid of that uh order status sort of purchased okay so i'm just going to go okay so i think we're making this table is becoming a little bit bigger but i think it's worth it because it's simplifying our model a bit so i'm going to change this to order details i'm going to disable this one so we definitely need to still bring all of this information into our model but we don't need it we're into our query editor but we don't need to bring it into our model okay so that's why i just put it down in the supported queries group and placing it into this group just again slides it down a lot for me so look how simple this has become for us so let's i mean hopefully this will work let's just close and apply and we'll see how this shapes up in our model now so all the details should just be a simple fact table and so yeah this is this is a brilliant example of um you know a lot more i would i would guess that a lot more work needs like for all of all of us needs to be done in the query editor then um then you think you can you can improve and optimize things so much more in the query than you think okay so a couple of questions here while we're waiting for this to load how do i get future dates in my fact table using incremental refresh um so i think there's a feature called instrumental refresh and power bi i haven't used it that much um and you can plan your date table that has future dates yeah that's easy that's easy enough to do um okay so now i believe we should be able to do this right so again i've just simplified it even more for myself [Music] i can have this one table down here which has all of my dates so i know it's not named very well but i'm just going to do similar things to what i did before i'm going to make this my active one [Music] make this the active one i'll turn this off then i've got my zip code here okay so i think i think we're all good now okay so now let's again just quickly come back here we need to change these around a little bit so we're going to put this to order details so my sort of calculations are going to be the same but i just got to reference different tables so i got details all the details and value okay so i would like obviously um cool okay so we're away we're away um laughing here now okay so this is all due to the fact that my my model is just like super simple now super simple started with complexity have really brought it back and made it super easy to understand okay so what can we do here we've got um the amount of orders by product right we can also um or total sales we could also do things like um you know anything to do with locations we could utilize the city and have a look at you know where where our sales are coming from a city perspective from a state perspective a heaps of little things so here is where we're just doing a little bit of data discovery and then we can work out quite quickly what sort of insights we want from their location state and we could probably get a map going pretty quickly with us so sp i presume sao paulo so paulo yeah okay and what are what are some other ones we've got sellers right sellers um well we could we can compare total sales total sales by state but we could we could also we could also look at total sales by um total sales by state for the um state for the sellers as well so seller state there's another way we could do it so we could sort of see okay where where are a lot of our sellers from and where a lot of our buyers from that would be quite this quite interesting one isn't it this locations probably has to link up to this this particular table here as well but it's probably a simple way we could we could we could work around that okay so let's crack let's start creating our visualization um i'm going to create a text box here i'm going to say um online sales and sites okay i'm just gonna quickly one of the um by the way one of the one of the big projects that we've got we've got going on at the moment that enterprise dna is we are um we are looking to build out some amazing uh designer templates for your power bi reports so if you if you come to our um showcase page here so if i go to enterprise dna showcase you'll see here that there is some incredible um power bi reports right and you can view these you can have a look at these uh yourself just go to the go to our showcase page um what we're going to do is is we're going to create a range of templates that our membership holders can download to really speed up the design aspects so it'll be things like backgrounds color themes all of your all of your visualizations preset and the idea is we're going to have a lot of these i'm going to have a lot of these templates and i think it's going to be huge um it really speed up the just design aspect of of how you create your compelling reports so we have a have a question here now this could be the case okay so is the granularity of the order payment the same result items it could have been off i just wanted to move on if i was doing this and i i wasn't under time constraints i would check it more i would honestly do a bit more auditing but in this particular case i just said okay let's just move on i wanted to um just quickly get something done there okay um so yeah yeah it could it could have certain issues but i just want to move on okay i just want to move on and um and and showcase some different insights okay so what i'm going to do here is how what sort of story do we wanna do we wanna tell here okay so i i think we wanna tell a story around like dates right so we wanna tell a story around um what's happening over time so this could be like our first table where we our first report page where we do a bit of an overview and to me time is is quite a important insight here okay we want to see how things are tracking over time so i'm going to do between so i think this is quite a historical data set now i'll show you a bit of a tip so um i know there was a question on dates as well before so what you can do um this is a little a nice little trick that i use um quite often is you want to have a big date range here okay so this date range you don't have to update it all the time so you can sort of get an overview okay what are the dates that we've got inside of our fact table like what are the dates we've actually got inside our data set so they're around to 2017 and 2018 in this particular data set okay this one has a lot more dates and that's why when i click here i have a lot of dates now i have this pattern which i have placed inside of my uh i place it inside of the analyst type okay so this is a pattern or formula that you can quickly um refine your your overall data set or all the dates which you showcase in your report to only those in your data set and it's a dynamic um the dynamic formula too it's a dynamic technique okay so i've it's in the community document of the analyst up and if you click on this it's got this pattern here you copy it if you go to your date table like so okay go to your date table then within your date table create a new column okay i know there's some good questions coming in so we will um we'll go into those so i'll just paste this in okay then then i want to find my start and end date okay so i'm going to go orders so let's let's just let's just find the purchase date okay so order repurchased okay and then i'll go max and all this is all this formula does is it creates a true and false if the if the date is between um the start and the end date of your actual data it's going to play it's going to create a true okay it's going to create a true in that column and then when you have that what you can do is you can in this filter pane here i can find that column i created called within range drag it to filter on all my pages and then just click the true data set and then now my dates are only going to be within the range that we have in our data set okay that is the best way to refine you know so you don't have all of these dates which don't have any data this actually returns only dates which have some data and so it can improve the look and feel of your model and prevents you from having to manually do anything because this is a dynamic calculated column okay does everyone understand what i just did it's there good little tip okay so let's have let's have a look at and there's some quick things that i can do here right i'm going to create some dates here so i could go total sales by date and then turn this into visualization like so so this is so we can very quickly now see how our sales are going by day okay some other quick calculations that we could do here okay um some other quick calculations cumulative so this is where we can quickly start using dax patterns right okay so things like cumulative totals moving averages um what are some other you know what are some other ones um percent of total okay these are all quick things that we can do now all of these by the way i put i'd use the analyst tab i use the analyst hub for these okay so all of these patterns that i can use i use i have my code already set up in the analyst lab so i highly recommend checking out the analyst hub it speeds up your productivity and this sort of pillar of power bi this formula or an analytics pillar like you wouldn't believe okay so i'm just going to come in here um and also i've got data so we can do some time intelligence right we can compare times okay so here i've got cumulative total example all selected so i'm going to click on this i don't i literally don't even i don't even write out formula patterns anymore i just quickly copy and paste that's all i do okay so um and this is something that i want you to try and do as quickly as i know i want you to speed up your um development as well by utilizing these techniques okay so cumulus of sales i'm going to create i'm going to create another one called cumulative orders as well so it's just that's slow here we get and you'll see that i can i can create up to 10 to 20 measures so quickly and then um very quickly i can throw my visualizations together because of the way that i can how quickly i can create all these measures so again i'm just going to go copy and paste here paste it in and it's the pattern is reusable because of the way the way that i always set up my models the same way and i'm just going to go total orders like so okay i'm going to again jump back to the analyst tab um i'm going to jump back here i'm going to find my some other code that i can use um well here's my here's some simple time intelligence i mean this is a simple pattern just using date ad sales last year i can just come in here paste it in i mean hopefully you can see i mean i don't even i don't i literally don't have to think about it i can just literally copy and paste got and then i'm going to do another measure here i'm going to do you know the only thing slowing me down here is actually power bi just calculating so slowly um okay new measure i'm going to do orders last year this is this is all about measure branching right so you know how i created my simple measures first well this is just the way that i can utilize those core measures to branch out into other calculations really quickly let's go total orders like so okay and then um and then check this out i can come in here and if i want to um go humorous of sales what what if i want to do cumulative sales last year well i don't need to even think about it i can just come in here copy this and until last year and then just change this measure rounds so okay and then i'm gonna just again copy another one and i'll do cumulative borders last year i'm intelligent we had a really great session on um time intelligence yesterday and the power bi accelerator enterprise dna power accelerator with brian and you know i i stated that you know i use there's a lot of time intelligence functions that you can use but i use these same patterns over and over again i don't i don't like have to use so many of those because like the patterns that i use are very dynamic right okay i'm also going to again come in here and just find like a moving average because i know that's a good one um averages over time uh no that's not one i want so let's just say we want to do a moving average do i have one in here moving average pattern see i've got my moving average pattern so i can just come in here copy the code and i'll just show you one example of what i can do here so just go and add another one in here i'm always trying to think you know how can we speed up our development in power bi and so that was one of my reasons for creating the analyst hub in the first place was um month was you know formulas i don't want to write out formulas right i just want to be able to copy and paste formulas so that's one thing we've solved and the other thing we're trying to solve is this design aspect for your reports is how how can we create backgrounds and designer kits that can speed up how you actually showcase your insights in a compelling way and anything else that we can think of around how you can automate uh more about what you do inside of power bi we are trying to innovate on so if you have any ideas um let me know what you leave me now if you can you think of anything to do with your power bi development that could be automated and how how could you automate it give me give me some suggestions i'd love to hear because if we can do it we we will that's uh that's definitely our goal okay so um so yes it's it's the community is pre-populated so all of these formula patterns that i am using are in my own personal documents but we have a community area which has 350 at the moment 353 dax formulas um that you can just copy yourself so you can literally copy them into your own documents customize them however you like um this is an incredibly versatile platform that you can um you know you can save code on power query um dax formulas anything really you can actually save scripts on anything save color themes so on and so forth it's a credible tool oh um it's being we've actually had our most popular popular month as well around users upgrading so so definitely worth checking out now okay so what are some of the other insights that we can showcase already right so some of the things that that i know we can do is we can do cumulative totals so because i've got those cameras of total calculations so let's just do a shorter time frame and let's see if we can do cumulative sales cool so now we've got a comparison accumulative sales comparison right so we'll do so it looks like our sales have increased quite a bit i wonder if that's right okay so let's put this down here okay so now we should be able to see how things are going you know we've got the ability now to filter very easily on different areas right we can quickly filter on different areas and find out how we're going one particular year to the to the next year okay so we might be able to if i um copy and paste this so here we've got cumulus of sales but here we might um we might want cumulative orders for example okay and then i also might want to overlay um my moving average here so i'm going to put this down here a one month moving average okay so starters come together so it looks like there's been a real nice uptick right in in sales if we look at it like that this is a this is this is because the data we haven't got as much data um that's why it's looking a bit weird i would say okay so questions questions um i'd like to mention do i get use no so the analyst tab is not part of membership um it's a separate application it is part of our center of excellence so our business customers do get it as part of a center of excellence package but for membership no at a design level creating themes to save design time i've spent hours working on visuals yeah so that isn't i totally agree i totally agree um we've got some amazing things in the works so definitely watch out there's going to be a lot more added to our platform around design templates designer packs you know i want to you know i personally um am a bit over having to customize my colors and i just want it all done for me or automatically so we're really working hard on that um i know a dashboard can be saved as a pdf but if the content say inside a table visual is scrolling yes unfortunately no you can't yeah that's exactly right you need to use paginated reports um stephanie if you want to um if you want the ability to um scroll between um long tables that have um where you have to scroll down um so that's unfortunately you know that's just one of the downsides that's just one of the functionalities that power bi doesn't enable um [Music] so yeah i think you just got to think outside maybe outside of what a table um you know think about different visualizations and try not to you know honestly the best way is just don't give someone a pdf just give them the online experience i mean they're still like on the computer right so um just don't give someone the pdf give it to them um and set up your dashboard in a way that is so compelling that they're just like i don't want to see this as a pdf anymore that would be my recommendation okay so this visualization um to me is not looking like that great um for some reason probably because you know has it been that has it been that big a difference would be my thoughts right maybe there has maybe there has so when we look at information like this right where we're looking that we are currently looking at what we've got selected here when we're looking at the information down here this is this is what our sales were the year before okay now this is a by state maybe this would honestly look better as a as a donut chart potentially and we'll just um i wanna i wanna i wanna round off the session actually doing some some like more advanced analysis right um that's probably my my goal from here is doing some more advanced calculations um no maybe this actually would look better as this okay so starting to come together i mean a map visualization would look good as well okay so what else can we put in here what else can we put in here so some things like um so top 10 sellers top 10 uh top 10 selling areas top 10 products you know those sort of things could be quite interesting okay so let's let's do that um now i'm just going to quickly come to the analyst hub just see if i have my ranking formulas all set up for me already maybe i don't so i got a topping example that's too complicated okay so maybe i don't have this set up exactly how i want but no no worries no worries this is what we can we can always we can always add right so what i thought would be nice is why don't we create a visualization that has like something some something down here where we have um you know top top five um cities top five uh what do we got top five products okay now to do that is not it's not that difficult it's just a little bit of a process okay so a little a couple of steps okay so what i always do first is i like to get things into a table okay and we'll do it by sales okay then i just build up my my my measures and i'm going to add this to the analyst hub in a second because this is something i do all the time so top i'm gonna go top um five locations okay and then do this so i'm going to go variable top five rank x all location cities so this is going to be a similar pattern over and over again and then i'm going to go by total sales okay then i'm going to go don't need a value descending and return and then i'll show you what this originally calculates out for um so question by jimmy i mean what we're looking at now is what the user gets to see so there's nothing um you know there's nothing too complicated about that like literally we are creating the the view for um for people within within our reports here okay so basically what i'm doing here right is i've created a rank that is the ranking pattern but what i'm going to do as well up here is i'm going to say if top 5 is less than or equal to 5 then return the total sales if not return blank okay so this is this is just a super common way to do this okay and then now if we look at this it's always going to bring up the dynamic top five for us isn't it and so even when i select say a different region it's only going to show me the top five which is which is really cool okay so i can select a different region here and it's going to just show me the top five so we've kept it we've kept it dynamic okay so then what i can do is this i can remove my total sales from here and now this visualization is only ever going to show me my top fives and because of the way i've named things everything is is um already labeled for me okay so i can bring this in here and maybe i want to bring this over to the side here then what i would do here is this so check this out this is how you again you can use the analyst hub really effectively okay so you've found the you've found a pattern okay i'm just going to copy this sorry i'll push the wrong button i'm going to copy that formula i'm going to come into the analyst hub okay i'm going to go to the app center i'm going to go to my dax and then i'm going to paste in that code okay go ahead and format it formats for me i don't the way that it formatted i don't love so what i'm going to do is i'm just going to i can actually edit within here okay okay i'm just gonna just format it the way that i like okay and then i can go commit and then so now i have this formula right i can actually i might rename it as well so i'll go top um top rank and then i can go commit and then i can save this formula top ranking pattern i can also save this as a community document as well if i want to but you can keep these private too okay so i've now saved that and then if i come into my documents here this formula pattern i can reuse over and over again um so if i come into my documents my top ranking pattern is now here and i can just quickly copy it from here on out so i can copy this and say i want to do a different top top one i can do top five products potentially i can then just come in here and say you're in it you it's very easy to copy from measure to measure within one model right but it's not easy to copy from measure um to measure in in different models and so um i'm gonna that's why that's why the analyst hub again can come in really handy there top five products okay and then i'm going to come in here products pull up that one mean that's all i need to do i don't need to change anything else i just go like that okay then i just copy and paste and then i just change the details inside of here and then i find my products and there we go got my top five products as well like so but that's a good little tip right and also i've got a little tip as to why naming conventions are so important because look at that look at that it's just a terrible title um and you know we could have made it a lot easier for ourselves so you know i could come in here and change it but i would generally go to the query editor but let's just do it here and call it product hey what else what else can we do let's go for another 10 minutes what else what other calculations can we we do i mean we go for longer if you like but all of this by the way is totally dynamic remember okay so um let's actually change this into a donut chart and we'll change a few things here okay we'll get rid of the legend and we'll change the detail levels up here just gets a little bit busy towards the end doesn't it should be okay because what i want i want the user to be able to quite easily select a particular region right and have everything filter nice like filter perfectly when you select something okay so one thing i need to do is put these on the same axis i just realized i don't want a secondary so some people don't like donut cats i use them all the time i think that they're absolutely fine um i think they they look they look great um personally you know you don't want to have an overkill of um you're the same sort of charts you want a bit of diversity personally there's my personal view but i mean how else could we show this so what can we put um what would be a nice chart inside of here i mean maybe maybe maybe honestly like a map would be nice in here that that that could be an option yeah a map could be an option too by the way for this one i mean let's like if i turn this into a map will it actually i don't think it's going to work but ah it kind of does it kind of does but definitely don't like that prefer the donut i mean there's there's other ways that we can make things stand out from a in a doughnut chart and we can change the radius yeah don't don't really love that too much but the only the only thing for me that i do agree with is it's a little bit overbearing like this is just a little bit too much isn't it like it's because it's got too many values so usually a bar chart works a little bit better if there's like a limited amount of values so you know that but i i do i do i do sort of agree with that you know the thing is like you want you want visualizations generally to be this if you've got the same visualization you don't want them to be scattered across the page so you know maybe maybe these down here could be better as tables honestly maybe these insights could be better as tables you know just something something like that um maybe that that looks a little bit better like so and with a table you can obviously you can obviously do a little bit of additional information okay so here's another little trick right we could um we've got you know what we could also do is we could do percent of total we could do percent of total and we could sort of see how much of a percent of the total is in this particular um this particular region or with this particular product okay so this is another little technique and i wonder if if here we actually have the pattern so let's go percent let's just find it center total okay so we've got a pattern down here percent of total um this one isn't this one has not set up exactly how i would like it honestly so let's let's see if i've got one set up in my own one scene yeah cool okay i've already got one see that total so i'm just gonna go copy i'll show you i'll show you a good little insight we could do don't i personally do not love um i try i do not love dream apps so unfortunately i'm not going to use that so check this out i'm going to um kind of sales locations okay and i'm just going to change up change of this to locations uh location city okay and so this pattern was already set up to give me the answer but remember if i'm going to put it in this visualization here if i do this it's not it's going to bring up every value and so see here it kind of ruins kind of ruins the um visualization a little bit so let's turn this into percentage i've got the scene of sales locations but what i need to do is i need to i need to rank i need to rank these as well based on the sales and so just as an example what i need to do is i need to come in here and go new measure paste it in um top five locations so i still want to rank based on sales but what i want to return as the value is that percent of sales okay so i want to go and find the percent of sales locations because this is this is a technique called ranked lists so you want to rank based on some metric but then you want to see just the the values for that for that ranking different values for that ranking okay so um what i want to do in here is now bring this this value here and it's going to bring me just the top like you see it's just the top five results and nothing else everything else is blank so it's a nice little trick okay so one and then i'll get rid of that um i can get rid of this one here so hopefully you know i i'm hopefully demonstrating to you that you can get a lot of these amazing insights so quickly without having to do anything that complex right like hopefully hopefully you're sort of seeing that the way i've built things up here has reduced my complexity quite significantly like everything when i combine all the my framework and um when i when i provide my sort of like development framework to all the sort of tools and etc that that we've got available particularly the analyst hub at this stage you know it can make a significant difference to your productivity in quite in quite a big way so we could also do something similar for um this as well if we really wanted to so let's and literally all i need to do is copy and paste i'm able to keep going honestly if you guys if you guys are i can award the free membership um pretty soon i'll make a decision on the free membership so it's never sales black name and then i'm going to again use this pattern here copy and paste by the way is just your best friend in power bi the top five products and then i'm just literally going to change this here to product like name okay so yeah i think from i think these tables honestly are probably like the best visualization because i just didn't want to have um you know bar charts in two different locations on my report i want to make it um like easy for the user to navigate around you know the key things from a visualization perspective is good colors you think in grids thinking grids around how you place things and um you know you want the user to be able to come in and understand what's going on you know within about 10 15 15 seconds okay um what have i done here so oh i didn't i didn't change this top five products what have i missed here products so you see here that i'm not i'm not really having to think through the lot because i just intuitively know it i've got the patterns already laid out you know it's it's literally just you know it's sort of just like building a puzzle i'm just putting a puzzle together at this point um so yeah i definitely want you to get to a similar sort of place like that okay so what else what other what else could we do what else could you do so quick um everyone write in what they what what sort of insights they wanna what they would like to see and i can see if i can quickly do it but i would probably get rid of the totals here as well they don't really provide much value we could um we could have some high level cards out the other side here total sales so people can just get an overview of overview of the full numbers right go to orders labels so we can get a ton of insights just from i think just from this particular already right okay what else can we we can see we'll figure out something there average order size over time yep so that's that's not too difficult cool thanks for all the thoughts ideas so one of the other things i would just quickly do while i'm building this up just need to make sure that we've got the right formatting i'll show you i'll show you another tip in a second so tool tips yeah i agree tooltips are pretty amazing right um i mean i can quickly i can quickly show you how to how to use those tool tips so remember remember that all of these calculations are completely dynamic right like i can change the time frame here and i'm going to get a change in all of my you know all of my insights which i'm reviewing and they're all going to dynamically adjust if i want to change the interactions i can come through here and just quickly do that so now so now this is going to be a like a full filter whenever i go for that it's going to do a full filter so we're quickly sort of seeing the outliers aren't we um quite quickly we're able to see you know and these are probably like black friday deals or some sort of like promotional sale that's being done um it's quite interesting um okay so yeah this is some good one some i'll do it and some i i think um we might we might leave for a later later point difference between average per day okay so okay so um sales over here what are some other interesting things that we can do we can do this is the date table that we have created right so i'm going to i'm going to i'm going to duplicate this page okay and i'll get rid of some of these visualizations so say for instance we wanted to work out something to do with like day of the week right like what is the what is the busiest day well this is where that date table is is so key right like the date table enables us to very quickly find this insight because we already have the day of the week embedded into it um so like if i come across here you'll see you'll see here that i have a day of the week name i do need to sort it so i'll go i'll sort it here sort by day of day of week okay and then and so the great said the great thing about this date too it's got day type as well like weekend or weekday as well so i've got this here i'm going to sort this by day of week and so even if i want if i wanted to compare weekday sales and weekend sales well i don't need to create any different formulas i can just literally use this dimension as a filter okay so if i wanted to have a look at my sales across the weekend and understand okay what is the performance of my sales in any any particular day i just go like this i can then drag this in right so i have all my days and then all i've got to do is grab my say let's go sales right and now i all of a sudden have a visualization which shows me the difference in days okay and then maybe i want um instead of having the full day of the week i maybe want the initial i've got that in my in my day table right so that is you know the one at the huge benefit of having a quality um a quality day table because all of these insights are super easy and then say for instance i want to have a look at okay well what sales did i make on weekends and weekdays we could maybe decide to use uh what do we got here day of the week type i think was day of the week day type right so i could i could i could just use it like this okay i could just have this as a um filter but also you know we could use this as a slicer as well maybe that maybe this would be more relevant as a slicer and so we could place this up here as a drop down like so and again i'm not changing any calculations or anything i could just select that and then all of a sudden i'm now looking at just my weekends right and we can have a look at the weekend sales over time and all of that is dynamic all of the calculations we are are doing we've created here are totally dynamic so what i want to just just while it's on top of my mind i want to show you one other thing that i would do so inside of here you see here that we created a lot of formulas quite quickly okay so what what i generally do in here to sort of this is this is a sort of new idea for me a new sort of framework that i use in this area is i create folders okay so i i select a whole range of measures like that and i put them into a folder and this is how i segment and organize my measures a little bit better inside of here okay and because i use patterns all the time the sense of totals like a lot of these names are always the same for me because it's the same same techniques over and over again and once you yeah this let's just organize things like like so much better than any any other way before i feel i used to create different measured tables major groups linking measures or something like that now but now i find like this is this is good enough right so i can actually multi-select here oh actually oh that's just two and i can go on intelligence okay and then i've got moving averages these just give you sort of folders or or buckets that you can utilize in the in the future um when you create and use similar patterns um for similar calculations remember all of these patterns really derive off of these core measures that we created right these two here and these are the ones i created really fast like really really quickly at the start and so i call those core measures because they're my my first branches okay so you'll see now that this is far more organized and then when i come in here it's the same i can come in here and go clips all and then all of a sudden boom everything is just laid out really neatly entirely for me to go and reference and for anyone to reference right it's easy for anyone to reference at a later point compared to when you just have all those measures listed all together okay what else what else what else okay couple more couple more um a couple more can we group by sales or order by day of the week seven lines are each yeah yeah i mean so you can easily do week week aggregations as well so again it just all comes down to the date table so in here there's fiscal fiscal week for example right so i'm just going to go fiscal week it's just all all about the context so so it sounds like there's a little bit of learning required around context there right you want to understand how context context manipulates your manipulates your me your your calculations okay so there's a lot of that covered in our um in our online platform okay well whilst um we've been going for ages actually okay so i'm just gonna do a quick draw on the winner of our um of our free membership account whether you've got membership or not it doesn't matter you can give it to someone else um okay so we'll choose navid if you um you have won the membership so i'm just going to inform my team that you have won okay but all you have to do is go to our also just write into our website come to come to our dna website co and um down the bottom in the footer you'll find the a contact us link so just say the winner of the free membership and i'm just going to inform my team that you've um you've won a membership now as well just give me a second i can keep going for a little bit though if you like team um we're building something pretty amazing here within a couple of hours so by the way um if you haven't um utilized this opportunity and you still want membership this this is ending in like four or five hours this um six thousand member celebration we've had this this last month has been our most popular month for membership ever so please take advantage of it if you um haven't already um this is this will be going away and probably won't be coming back for for months um this sort of pricing so please if you want to be part of our platform part of our community definitely check it out just as a quick overview we have our membership portal and we have our coe portal coe is more for corporates more for teams but for membership we have enormous amount of content and resources i mean it's unmatched basically and i feel like the pricing that we put out on it is just crazy low for the benefits that you can get out of it so definitely research this a little bit more if you aren't a member or aren't part of our community and would really love to get you involved okay back to this that visualization is not very good so what are some why don't we just do like a quick overview like what are some other patterns that we could use okay so i'll just jump to the analyst hub quickly so these are the ones that i have in my personal documents right so we've got percent of total we've got abandoning banding so this is a you know dynamic grouping we could group our um you know we could group our dimensions in many different ways we could group our sellers between our top top sellers versus our bottom sellers or average sellers um a whole whole range of different ways we could do that so bending is another one there's another good technique and that's the pattern you would use um we've got switch true logic is another one cluster analysis so here's i mean it's actually quite similar it's similar to similar to the grouping pattern with a with a slight difference so that's another another good idea that you can use um sales of bottom 20 so we can sort of create like a specific segmentation we've got averages what the averages are per month uh what else this one here is is um calculating mo like if you purchase multiple products so if a customer has purchased like trying to trying to work out has a who of our customers have purchased more than once from us basically so um like cohort analysis type of um type of thing okay i'm getting i'm getting a little bit tired i think so we're going for this is one of my longest webinars ever um this this is a cool example though so you know obviously i mean there's so much more we can do right there's so much more we can do but i think i think probably done enough for now um and i think we'll round off hopefully i've given you like a whole load of ideas around what you can do um and and and different techniques you can use i mean we literally have not even got started in terms of like visualization techniques i mean if you if you come to our membership portal and you go to our showcases you know you'll find so many different ideas so many different techniques um so many ways you can build awesome navigation experiences build application like experiences in your reports um so definitely check out a lot of the ideas in here and definitely check out more of our content online um on our platform etc there's uh you know there's so many other ideas and techniques which are covered um that i just don't have time to cover in this particular environment uh or this in this particular session um i wish i wish i um could do this all day but it's quite tiring it is quite tiring honestly um but yeah definitely check out i mean like check out just our power bi content you know we're we're slowly moving into power platform content um we're also by the way we're also moving into python content we're releasing a python python course in the next week we're going to get into all the other power platforms like um by you know ai builder virtual agents we're trying to get content around that sql r machine learning um we're really evolving our content around tools that support power bi power bi is still our absolute core um but there's a lot of ancillary tools which can really support what you do with power bi and so um we want to make sure that we have the best content on that as well so watch out for more of that being released in our in our learning center soon and more resources coming soon as well um a lot of a lot of really cool ideas and innovation we're building our team we're really growing our team as as quickly as we can um as as sensibly as we can and you know we want we want more content and more resources we want more tools um you know we want to we want to continue to um empower everyone around um you know these these amazing tools and what you can achieve internally okay that's it that's it i'm gonna round off i've talked enough um power automate desktop yesterday so this is on the cards as well power power automate desktop i was personally going to create a course on that myself but i have been um just being pulled in a lot of different directions lately so i just haven't been able to um been able to do it uh so but definitely absolutely power bi power automatic desktop is pretty awesome so flow doesn't exist anymore it's called power automate um power apps yeah so power apps yeah so we've already got content on powerapps there's more coming um so definitely watch out for that okay everyone really appreciate it thanks for a fantastic session um really enjoy these like i just want to do more of these um really really um appreciate all the uh collaboration and love um love being part of this awesome community so appreciate it look forward to seeing you next time see ya

Channel: Enterprise DNA

Views: 3,278

Rating: 4.9565215 out of 5

Keywords:

Id: 72-WjD8kKVc

Length: 112min 33sec (6753 seconds)

Published: Thu Sep 30 2021