CALCULATION GROUPS to REUSE & REDUCE MEASURES // Beginners Guide to Power BI in 2023

in this video we're going to go through how you can start creating your own calculation groups to reduce and reuse the measures that you have we're going to go through how you can get started with it and also go through some of the example scenarios in which the calculation groups would be useful all of that and more so without further Ado let's get started hi my name is Fran and welcome to the solutions abroad YouTube channel work over tips tricks and best practices when working with power RBI I upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so the model Explorer is a new feature that came out as part of the October's feature update for powerbi and along with it it exposes the semantic model of your data sets which are basically some of the properties that you previously couldn't access like the calculation groups and this calculation groups is what we're going to focus on for today's video so calculation groups is actually not a new feature and it's actually something that I covered a few years back however when I made that video you could only kind of customize or edit your calculation groups through a third-party tool like tabular editor however with the October update you don't really need tabular editor anymore at least for calculation groups because you can do everything now in powerbi desktop so since the model Explorer is a preview feature in the October version you first just need to make sure that you download and install the October version of parbi desktop once you've done that you just need to make sure you enable it under file options and settings and under options you will need to find it under the preview feature section down here and just make sure that it's enabled here model Explorer and calculation group authoring if you hit okay restart your powerb desktop that should enable the calculation group authoring for you you'll know that it's enabled uh if you go to the model view on the left hand side here you will see a calculation group button here that lets you create a new calculation group from the home ribbon and you'll also see this new model tab here on the right hand side which gives you a whole list of new features under the semantic model so to get us started I've already created a sample data set here just so that we don't really need to create it from scratch this is the typical Northwind data set which is a subset of a fictional company that sells Goods internationally so as you can see here in the model view I've I have already a few tables set up the relationships are set up but it's not really the focus of today so I'm not going to go through explaining how they are connected but if you go back to the tables here you will notice that under calculations I've already created a few measures well actually let's go back to the report View and I can show you what I've done so we have a few measures here that I've already created which is just a calculation of the Northwind data sets in different aspects so at the moment we have in this table that I'm showing here in the report view is the total gross sales for every single month within our calendar now the gross sales is basically just a multiplication of the unit price and the quantity however we have other sort of calculations that we are doing like net sales which is your grow sales minus any discounts or maybe the quantity of the unit sold or maybe the average unit price so I've just created the measures just so that we can use these for our kind of scenarios and then what I've done is I have created a group of different measures here which is just a different slice or comparisons for the gross sales so let's look at one of them so for example I just uh select gross sales previous month here pm and as you can see all it does is it simply calculates the gross sales but it just gets the previous month sales now this kind of calculation is typically useful if you want to create a month-on-month percentage or comparison in your reports so if you drag that into a table like this for example it's fairly self-explanatory so in the month of August you have £ 26,000 in gross sales however in the previous month which is July the grow sales is 30,000 which is similar to the gross sales for July so that's what you would expect these comparisons to do so as you can see it's very powerful a lot of these calculations and you know I've only created a few examples here just for us to use and um as you can see it's all tied to the gross sales however let's say you want want to make these comparisons in the net sales calculation that means that for the net sales if you want to create a net sales comparison against the previous month you need to copy this measure gross sales PM copy this code create a new measure and replace the calculation to be calculating the net sales effectively duplicating the previous month Dax measure so and imagine that we have a few other measures here that we might want to compare so maybe we want to compare the net cells against the previous quarter so that means we need to create those measures and then maybe we want to compare the previous year unit sold in which case we need to create another measure for that and as you can see it sort of starts to duplicate the work but kind of returning the same thing and this is the kind of scenarios in which calculation groups can help you with so to start using the calculation groups let's go back to the model view here and let's starts to create a calculation group so you can do it by either creating a calculation group from here just clicking the calculation group button from the home Ribbon or from the model tab here so when you create a new calculation group it will automatically create a few things for you it will create the calculation group table the group column as well as the item so one of the measures that you will need to import from the comparison measures that we've created so we're going to leave this as it is for now the selected measure and we're going to do a few things so we're going to just name or rename some of these groups so that we know what they are so this is the comparisons table and then the column will be will also call it comparisons and then under the calculation item actually under the measures uh let's look for one of the comparison measures that we were referring to earlier so this one gross sales PM so we're going to copy that under the calculation item the first one that it's created we're just going to replace that with the measure that we've just copied we're going to rename it and make it more generic so we'll call it previous month and then instead of referring to over here a specific calculation or a measure we're just going to use selected measure so this is the bit in the calculation item that make sure that it is generic so when you use or when you add a measure in the context of this calculation group it will create a previous month calculation and basically reuse the same measure so if it doesn't make any sense to you yet um we're going to go through it in a little bit but for now I'm just going to double check and make sure there are no errors here so just add a closing parenthesis there for the calculate and if we hit enter that uh should be it done for our first calculation item in our calculation group so now let's go back to the report view so now let's delete this table and let's do a few things so let's create a new Matrix here so in The Matrix let's add the month let's add the month in the row here and in the column let's add the comparisons and then well let's see what the issue is the that's fine and then on the calculation let's see if we can add gross sales here here we go so what it's done and it doesn't look that obvious just yet because we only have one measure here and we only have one value that we are calculating is is getting the previous sales for the gross sales now at the moment because we only have one it looks like the just the calculation for the previous month however the power comes here is if we add net sales as a new value in this as you can see it gives us the previous month's gross or net sales for the previous month that is in the current context so you can add as many of these as you want average unit price unit sold and it gives you the previous sales for those different calculations so now we basically don't need this gross sales pm and in fact we can just delete it because we don't really need need it anymore and we can replace all of these other measures here to be part of the calculation group let's just do that quickly so that I can show you kind of what is the power of this measure for so here we go so I've created or recreated those measures as calculation group items instead in this the one that we've just created now if we go back to this table you'll see that there are far more other things that you can see here now that you previously aren't able to do or rather you'd have to create individual measures for each of these to calculate them but now you don't have to because of calculation groups so now what you can do is you can just simply go and delete all of those different measures that we are using to compare because we don't really need them anymore since we have calculation groups Now set up there are a few things that you can do and let's have a look at what other things you can do with the calculation group option here in paria desktop so at the moment we are showing all of the measures and all of the different calculation items in this calculation group however you might not want to use all of them so in which case what you can do is you can use the column which is part of the calculation group here as you can see here is basically a table and column combination you can choose which one you want to use if you want to use all of them or you want to use just some of them so for example you might want to just see the previous months across all of these four different measures so you just tick that and it will just give you the previous month so tick or just select the different comparisons that you want to use another thing that you can do here is to adjust the order of your comparisons your items and how they show up when you show them in a matrix like this so if we go to the model view and you select the calculation group you will have or you should have this option calculation item order so just drag them up or down as you need them and that will adjust their order so another thing is that on these individual items just because they are treated as measures you might want to think about their Dynamic formatting so for example if they are or let's say typical scenario would be to create a calculation item for a year on-year percentage if you want to make sure that your calculation item is showing in a percentage value you have that option to kind of add that Dynamic formatting here in this option here so you can just enable Dynamic format string and then adjust that to what you need and that's really it for this video about calculation groups now I covered the sort of the basic elements of calculation groups and how you can use it however if you want to read more about it or read the documentation to get a more detailed view of this feature I'll leave a link to the documentation in the description box below thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so not to do better for next time ask your questions in the comment section box below so I can help you and you can help others if you like this video we have a patre page where you can support the channel and get exclusive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye-bye

Channel: Solutions Abroad

Views: 6,590

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi 2023, power bi calculation groups, calculation group, reuse measures, power bi modular, power bi reuse measures

Id: vAnb3e1NCjM

Length: 13min 12sec (792 seconds)

Published: Wed Nov 29 2023