Unlock the Full Potential of CALCULATION GROUPS in Power BI Desktop | Oct 2023 Power BI Update

today I'm going to show you how to create a calculation group in powerbi and guess what we are using the new feature released in the October 2023 powerbi update so that means no more external tools no more tabular editor we are using just the new feature that was released in the October 2023 powerbi update so guys this is bananas and let's do this before we get started with the example real quick here let's go over calculation groups in powerbi for those who are totally new to powerbi desktop or maybe totally new to calculation groups here we have a few Concepts that are worth mentioning so what is calculation groups like it says right here it's a simple way to reduce the number of measures in a model by grouping common measure Expressions so no more readand measures with this methodology so what are the key benefits here it reduces the overall number of measures to create and also maintain second Point here it allows you to apply Dynamic calculations to existing measures and we have an example here you will see how it works and another cool feature here is that you will be able to use measures with slicers which is impossible in real life and then finally a couple of important points here we're going to be using the selected measure function which acts as a placeholder for the measure to which the calculation adding will apply something also really really important here to be aware of is that calculation groups works with explicit measures so what are explicit measures those are measures that you manually create and then to give you an idea about the final results if you go over the model view which is this one right here you will see this option model and then when you create calculation groups you will see here the first option here the number of calculation groups that you have for this particular case we have just one and then the three main parts for this calculation group are the following the name of the group the first one for this particular case I'm calling this time intelligence and then the second part here is the calculation group column name I'm calling this time calculation and finally we have the calculation group items so these are basically the calculations that you're going to be performing we're going to be using here Dax measures and the selected measure daxun function is key to make this feature work so now guys no more talking and let's do this so this is the example that we're going to be creating I'm going to share with you this information as well if you want to follow alone the first thing you want to do here is download the latest version if you don't have the October 2023 powerbi update you need to go to the website and download the latest version once you download it install it just follow the steps so once you install the latest program the October 2023 parv update update you can go over here file once you're here go over options and settings options here and remember this feature is still in public preview so you need to activate this okay go over global and then look for preview features so what you need to do here is you need to check the last option model Explorer and calculation group so once you are done with this hit okay and you're good to go for this particular case I already done this so I you don't have to do anything here once you do that you need to close your current file and then open the file again in order to make this work once you install everything set up the preview the next step is to go over model view once you are here you can see an option here called calculation group hit this option and let's start creating our first calculation group so once you do that automatically you can see here the options that I just described so let's rename this this this is the name of the calculation group so let's call this time intelligence perfect now let's rename the second part here which is the column name for this particular case let's call this time calculation so once you're done with the column name the next part is the calculation items and by default we have the the first calculation item here which is the place holder if you remember select the measure you can rename this if you want let's call this current once we do that this is the time to add more calculation items right click new calculation item so let's call this mon to date how about that and then once we are here what we need to do is let's use calculate here and we need to keep here instead of the measure we need to have here the placeholder selected measure once we are here what we need to do is we need to use this popular function time intelligence function dates mon to date and then here we need to reference the DAT column from the calendar table once we are done with this close parenthesis and let's approve let's see what happens perfect we have mon to date we can even copy this so we don't have to recreate anything from scratch right click again new calculation item for this particular case we're going to use here quarter to date okay quarter to date and then here just change this to quarter and then we are good to go you can copy this again before you approve it so we are done with coder to date right click let's keep working Fox and here what we need to do is year to date so let's update here to year to date as well there it is so once you're done approve these changes and now you have year to date what we need to do next is to create previous month so let's do that previous month same story here let's use calculate and once we are there you might be familiar with the date ad function so we are referencing here the date column from the calendar table minus one and then this should be month so that's previous month you can copy this if you want approve the changes now we have previous month so let's keep going here right click again so now let's do previous month quarter to date how about that for that one what we need to do here is we need to reference one of the calculation items this one right here and this should be month to date and then we should be good to go let's see I think we have this parenthesis we don't need to have that parenthesis now we're good to go so let's approve the changes let's keep going here right click here again and remember that just copy the previous me me contrl V and then for this particular case let's do year to date what we need to do here is we need to reference year to date let's approve the changes again okay so let's keep going folks contrl C again right click new calculation item and I forgot about the previous month month to date so we need to do that as well cool cool cool and now let's work on the month over month variation new calculation and now let's call this month over month for this particular case we're going to use variables okay if you're not familiar with variables I'm going to share with you a link as well you can stop this video and check that tutorial out so let's call this variable current selection so we have right there so the next variable is going to be previous month so once we are there so we need to use here the calculate function as well and then select the measure so perfect let's get another variable here let's call this Ratio or growth and here we need to use the divide function this should be current selection minus previous month and we are referencing here the variables and then this should be previous month and finally we need to use here return and here we need to reference the last variable which is growth and then we should be good to go so let's see what happens we can also add here percentage just to make sure that we are talking about percentages here let's approve the changes we are good to go another cool thing about this feature is that you can rearrange here the calculation items let's do that real quick if you select this option here the calculation items option I care about current first and then month to date quarter to date year to date previous month and then then we have previous month month to date quarter to date year to date and then month over Monon percentage so this is looking awesome now let's play with the results so let's go back to report here report View and I already have this set up so you don't have to work too much here the first thing you want to do here is to create two tables the first table or the first metrix here is the sales performance check this out so what you need to do here is you need to drag the time calculation column from the time intelligence table and this is basically the calulation group that we just worked on so let's drag and drop here and let's see what happens so this is not working yet because we need to add values which is a measure that we already have in the model for this particular case since we are talking about cells here the title says cells so let's go over Dax measures here and let's go over cells and let's use total cells and as you can see real quick you can see the results here so we have a few options here we need to format this because it's too busy in my opinion but it's working Fox another cool thing is that you can use this as an slicer as well so let's Dr time calculation and drop it here boom so now you have this slicer and if you start playing with it you will see the results that it's changing dynamically check this current just current month over month just month over month year to date just year to date so it's working perfectly fine and then you can do the same thing here and then you can do the same thing here for profit so let's do that let's select the Matrix here once we are there let's go over the data option let's drag time calculation again and drop it here once you do that you have this message as well and then for values you need to go go over Dax measures and let's go over profits and let's select total profits so once you do that you can see profits here as well so it's working perfectly fine let's remove the filters and there you have it as you can see this is too busy what you need to do next is we need to give this a better format so let's do that for percentages we're want to see percentages here and then for sales and profits we want to see also just Millions here so let's do that real quick so let's go back over the model view once we are here let's go over calculation groups and then let's go over this measures one by one let's get started with percentages for percentages we want to see percentages right so that's our goal what we need to do here is let's select the measure or the calculation item and then as you can see here there's an option Dynamic format string so let's activate this yes once you do that here you're going to add the new format for this particular case we're going to use this and then percentage then we should be good to go let's approve this if you go back to the report now you see percentages it's working perfectly fine so let's go back to model view and let's keep doing this for the other measures so let's get started with the current measure right here Dynamic format string turn this on then here we need to use something similar we need dollars here this symbol here again boom and then we can copy this format because we're going to apply the same format to the other measures contrl C let's approve the changes if we go back to report view now you can see this is more readable and we can do the same for the other measures let's go back to model view so let's do this quickly for this particular measure we need to update here quarter to date I just cut this so let's keep working here and now guys we should be good to go let's go back to report View and let's see what happens boom so now we have these visuals they are more readable and then you can like I said before you can start playing with the filters here and's see how it works month over month it's working perfectly fine previous month month to date you see the result there as well if you want to select just a couple you can do that as well let's say current for now now and then how about year to date hold down control and select year to date and you can see the results just for current and year to date so this is working perfectly fine like you see right here this is really really powerful my friends I'm very excited about this new feature I'm pretty sure they will be improvements but this is just a good beginning there you have it my friends really really really powerful feature in this new release the October 2023 powerb update so let me know what you think let me know your thoughts in the comments below also check these tutorials out if you don't want to miss anything thank you guys for your time and see you in my next tutorial

Channel: Nestor Adrianzen

Views: 8,767

Keywords: nestor adrianzen, power bi tutorial, power bi tutorial for beginners, calculation groups in Power BI, October 2023 Power BI Update, calculation groups in power bi desktop

Id: P00HRZ8V_Ak

Length: 15min 42sec (942 seconds)

Published: Sat Oct 21 2023