REDUCE the # of measures with Calculation Groups In Power BI

yo what's up it's patrick from guyanacube and in this video i'm going to talk about why calculation groups are so important and i'm going to show you actually how to create your first calculation group stay tuned [Music] with this new introduction of external tools in the power bi desktop now we can directly connect to tablet editor and there's some new capabilities and features that's just right at our fingertips and one of them is creating calculation groups okay and so i decided to do a video to talk about why calculation groups are so important how they can help enhance your model and show you how to create them okay and so why are they so important that's where i want to start i want to start why are they so important imagine you have a model and you create a couple of measures let's just say you create two and then someone says hey patrick i need a month-to-day calculation and a year-to-date calculation and a previous year calculation and a year-over-year calculation what do you have to do you have to create a correspondent you have to create four or five corresponding measures for each measure in your model that is a lot of work with calculation groups you can minimize that work and that's what i'm going to show you all right that's why they're so important so instead of all this talking you guys know all i like to do let's do what let's head over to my laptop imagine you have this model that that i built and i have a slicer on this page and we'll get back to that in a little bit if you expand internet sales you'll see i have two groups right i have a folder that has all my measures in it i have two folders under that folder one for quantity and one for sales and so i started out with just this this quantity measure and the sales measure then the requirements screw they say hey patrick we need a quantity month to date and a sales month today we need a quantity previous year and a sales previous year and on and on and so forth and you can see how my measures just exploded and i wasn't happy about it but these are typical requirements that you know that must be met when you're designing a model that's built for reporting and so how do you minimize the number of measures you create calculation groups let me show you you can see i have all these measures i'm going to eliminate the need for those measures by creating a calculation group right there's a couple of ways you can go about this but the easiest way is if you're running the july 2020 version of the desktop you'll see this external tools option in the ribbon go ahead and click it if you downloaded the latest version of tabular editor you'll see tablet editor is one of the external tools go ahead and click it and what's gonna happen is it's gonna open up tablet editor already connected to my model this is great it's awesome head over here to the model and you'll see lots of different folders we're gonna focus in on our tables because this is where you can create your calculation group right click on the table choose create new and select calculation group from the menu that appears and then you'll see a new calculation group you can call this whatever you want let's just call ours time intelligence okay if you expand this out this is just a single column table okay it's going to have one single column in it with multiple rows and these rows will be based on the calculation items that you create the calculation items are synonymous with those time based calculations that you want to add in your model right and so let's say i'm going to start with month to date okay and so we're going to right click on calculations item and say new calculation item and we're going to see this month to date press enter and then you have your little dax editor here to do month to date it is really really easy right the easiest way to do this you can type it and freehand this or you can go back over to your power bi desktop expand out your measures and just look for one of those month-to-day calculations click it go ahead and copy the formula everything to the right of the equal sign get that on your clipboard head back to the tablet editor and paste it in now this is the beauty this is the beauty i'm about to show you the magic of calculation groups instead of explicitly entering the measure we're going to use a scalar function that returns the selected measure the measure that's used in the context of the visual that you're creating the matrix the table whatever it is right so we're going to replace that with selected measure so if we drag sales to the table it's going to calculate month to date for sales if we drag quantity it's going to calculate for it's going to do that for quantity okay and then if we want to check this out if we just go ahead we got a couple of things we can do if we click right here format that format works but you're syntactically correct and then we're going to repeat this for every one of those time based calculations so i'm going to do this and adam may speed this up while i quickly add all these all right that was a lot of typing but you need to do that once for each one of those time based analysis you add those calculation items and they'll actually when you when we finish this up you'll see it creates a nice little table with a list of those calculation items and we can use those for some some neat little tricks inside the desktop all right back to my desktop so now that it's all created we have all of these created all of our time based calculations created all we need to do now is click file and click save and if we head back over to the desktop you'll see it says hey we need to refresh because it's creating a table and it needs to refresh the contents of that table so go ahead and click refresh and what will happen is it's working on it if we collapse this out you'll see our new time intelligence table right and you may be thinking to yourself well patrick it's just a name it's just a single name there don't worry don't worry watch watch how cool this is watch how cool this is we're going to add a matrix to our design surface and then we're going to add just the sales as the value okay we're at our sales as the value and then what we're going to do is we're going to add a little hierarchy and on our rows we're going to go year and so we can expand collapse you can see we can expand and collapse and see the values in there all right really cool and now what we're going to do is we're going to take the name in our time intelligence table and drag it to columns and then what you'll see is all of those calculations are just added i don't need to use all those individual measures automatically it uses each it uses the name and it performs the calculation that i created for each calculation item over the given measure that's within that context now i do one more thing i do one more thing to my um to my measures to my measures um that makes this a little easier because i also want to see the actual current value right so let's head back over to tablet editor i'm going to add one more calculation item we're gonna call it current and then we're gonna use that selected measure function i'm gonna click the formatter and then we're gonna go ahead and save it yes head back to the desktop click refresh it's refreshing and now you'll see that current my current sales here if i look at the previous year you can see how it's just working right this is great this works so phenomenal and i don't have to create a million measures i create my calculation group grab the measure i want drag the measure i want in the context of the visual right add it to that visual and then use the items in that name column in my calculation group and i can see the different calculations that i've created and i know what you're thinking patrick but what if i only want to see the previous year the current year the current year and the previous year are a multi selection of these well guess what it's so easy to do let me show you head back over to the design surface expand time intelligence the name here calculation group drag name right there and we're going to make name a slicer and what we're going to do with it we're going to say go over to format and go over to selection controls and we're going to turn multi-select with control off because we just want to multi-select and now i'm going to use the current one and i'm going to use previous year and now you can see right i'm only using those two maybe i only want to do year-over-year and you're over your percent and you're probably thinking patrick i want to format this well guess what if i head right back over to tablet editor i can click on this and you see the format string right there i can easily type up my format string 0.0 because i only want two decimal places percent and i'm going to click save yep head back over to the desktop and boom now i have percentages now you may be thinking patrick can't we do this dynamically with calculation groups of course you can and if you stay tuned to our our channel i'm sure one of us adam or myself will create a video on it all right what do you guys think are you using calculation groups have you encountered them what do you think about the video do you have any questions comments you know what to do let's continue the conversation where in the comments below it's your first time visiting the guy in the cube channel hit that subscribe button you like my video big thumbs up as always from adam myself thanks for watching we'll see in the next video

Channel: Guy in a Cube

Views: 88,179

Rating: 4.9799933 out of 5

Keywords: calculation groups, power bi, power bi calculation groups, power bi desktop, power bi desktop calculation groups, power bi desktop tutorial, power bi desktop tutorial for beginners, power bi tutorial, powerbi desktop calculation groups, tabular editor calculation groups, tabular editor power bi, tabular editor

Id: vlnx7QUVYME

Length: 9min 24sec (564 seconds)

Published: Wed Aug 05 2020