[Music]hi everyone it's Sam McCoy here todaywe're going over something reallypractical we want to discover what arethe best selling products in aparticular region and we might want tosee some sort of sort of correlation Imean that that's why we would do this wewould want to see well are there somecommon is there some common productswhich sell really well across all of ourdifferent regions to all the differentareas that we might be selling to nowit's not difficult to be think about itto work out what the total sales are forexample of every product per region butwhat could happen is if you do that isit can get you can get a really reallyreally busy chart and you might actuallyall visualization and you might actuallyonly want to focus in on the top ofproducts so if I just mock this up andthe example I've got here so I'll takeout the calculation I've already got andI'll put in total sales now if we lookat that you'll see here that we sell alot of products right we sell a hugeamount of products and that list can getreally long in us very actually it'squite hard to actually see any trends orto see the you know to see thecalculation or the insight that we wantto see so what we have to do is we needto somehow make the ones that are not onthe top to is what the example we'regoing to use here though the productswhich are not in the top two we need tosomehow make that blank so that itdoesn't actually show up in ourvisualization here and it's not as easyas you think there's a little bit to thedecks formula that we're going to writetoday and there's a little bit you knowof understanding that's required toactually understand how why this insightcomes about so there's certainly hasmany reasons why you would actually doit and and it's very useful you can useit for a range of different you know arange of different scenarios not justselling be selling products could bebest customers best selling regions be asales people you could goyou could even you know isolate profitmargins profits cost yeah there'sthere's so many different ways youactually use this this pattern thatwe're going to use but no the greatthing about it is we can change thecontext of the calculation and it alsowill adjust the table so you know thisis a perfect example so this is a muchmore contained visualization it's veryeasy to see these are all the places wesell the top two people we could wecould sort by the product name forexample and we could see well is therean our best selling products and allthese regions as there any commonground' he I mean is there anything thatsort of pops out to you and says okaywell this is by far our best sellingproduct that could determine yourmarketing promotions your advertisingyour new deals that you might put on soon and so forth just to remind that youcan download this resource and all theother training resources are throughenterprise DNA TV link is in thedescription it just requires a smallpurchase okayso let's jump into it let's start fromscratch here so I'm just going to jumpto my demo model I'm going to show youthe formula that you need to write toactually make this happenokay so let's just set the scene herelet's go and grab let's go grab ourstate where she will go state code thereand we will go total salesso let's actually leave it as a map hereokay and then we want to grab we'regoing to grab our the name of eachlocation within our state within ourstates and then within that I also wantmy productsokay so we need to so we need to avoidis bring in total sales is obviouslygoing to show total sales for everysingle product for every single regionwell I want to isolate I only want toshow the sales of the top two productsin every region then I want to blank outeverything else okay so to do that to dothat we need to utilize this formula soI'm going to call it top two productsper region now as I mentioned you canuse this in so many different ways so socertainly you know it's justunderstanding let's chasm of you likeyou know this this this way that weactually find this answer I mean youcould apply them and multiples ordifferent ways okay so I'm just goingthrough I'm going to use B variableshere I'm a big fan of variables so I'mrecommending to get into them as soon asyou can and have a good understanding ofwhat you can achieve within just gonnaput a ranking context in here we'regoing to use a function called toppingwhich has become a new favorite of mineand then I'm gonna find my product namebecause that's actually what we're goingto rank here if you think about it we'retrying to find the top two products sothe ranking context is going to be justthat okay and then I'm gonna go returnand dump down to a new line duringcalculates and then I'm gonna obviouslywe're ranking where we're calculatingsales here right so we want we stillwant to calculate sales we just want tocalculate in a new context and that'swhat you can do inside of calculate andhere's top in I'm gonna say okay wellthe in value is two because we want torank or only find the top two then I'mgonna go all here I'm gonna go allproduct name I'm gonna close that offand then I'm gonna rank by the salesright now ranked by sales and that's allwe've got to do for top it now the lastthing I've got a jobs we've actually gotit because of this all statement here orthis all function we've got to actuallybring back the context of the productname and that's where this rankingcontext comes up and so if I go okay andthen I bring this in I'm just going toprofile and make sure that it'sformatted correctlyif I bring this into this table you'llsee now check this out check this outthere's actually blank values for all ofthe products which are not in our toptwo so if we if we say let's have a looklet's actually let's actually sort theseso you see here now but these are allour top two products in any specificregion and that's why you know up herewe're getting a lot of values and thenthey start to peter out and then all ofa suddenwe'll get you out a bit lower and you'llsee that we have everything's blank andthat is because now we've isolated thetop two products and dynamically viathis formula and we're only showcasingthe sales for those two products andthen this obviously links back into thedata model just like every and you knowjust like most of the calculations we doand that's what we want to achieve nowif I get rid of total sales this tableis going to become a lot smaller andit's only going to showcase the top twoproducts will the sales of the top twoproducts which is awesome and then wecould you know there's lots of stuff wecan do around visualizations data barsis a good one and we could bring in somecolor to this and again we could have alook here and we can see okay well itlooks like well you know product one ispretty well sold but you know it lookslike product twenty-eight producttwenty-eight is you know very quickly wecan identify well this is a pretty thisis a this is a top of product in a lotof areas so pretty good inside rightpretty good way to find you know toquickly discover things you know basedon ranking in as I mentioned you coulddo this so many different ways it'scrazy you know how quickly you couldreuse this you could just copy and pastethis you can put it into customerssalespeople you know any don't mentionthat you think of can think of all thatyou have in your data model you coulduse this in in bring us insight intointo your reports okay let's roundthings off there hopefully you like thecontent if you did throw the video alikereally appreciate itand don't forget to subscribe as wellfresh power bi content every weekday allthe best with this technique absolutelysure that you'll be able to utilize usin many different ways it's a reallyreally quick and easy way to discoveryour great insight so all very best inspeaking next time
Source
