Revisiting Common Calculation Group Patterns in Power BI!

hey data fans Reed here today I want to revisit the topic of calculation groups now I've done a lot of live streams and videos in the past on the subject but a few weeks ago we did get the new calculation group option to be added directly from powerbi desktop plus the new semantic model view and now with that topic in mind and the fact that they can easily be added from powerbi desktop I wanted to revisit three of my favorite calculation group patterns now I know there's a lot of different ways to utilize those but these are three of the standout or Flagship ones that I typically use calculation groups for so I want to revisit those but from the concept of building them inside of powerbi and just to show you what some of those ways are for those of you less familiar with calculation groups now that they're finally available in powerbi desktop so let's go ahead and hop into powerbi and get [Music] started so to start this conversation I'll discuss the tried andrue pattern to begin with which is usually something something related to time intelligence in this case I have a calculation group that on this page is displaying either options for quarter to date or year to dat I have the slicer over here for that calculation Group which allows me to alter the visual below it between these two categories so you do have flexibility to use calculation groups as a slicer you can also leverage them on a visual here which can be added into the column section of a matrix you also if you wanted to even have options to apply it as a visual level filter or a page level filter to affect any of those measures so just as at brief recap calculation groups are very useful to be able to apply packageable logic over any number of measures at the visual page or report level but this one's one of the most common patterns you'll see and again as a reminder of where we can actually create these now that we have this new model view which as of October of 2023 is in preview so you need to turn that on in your file options and settings options and preview features down here which is the model Explorer and calculation group authoring that just needs to be turned on eventually this will come out of preview depending on when you're watching this video today it needs to be configured and then from your model view over here you have the option to create those at the semantic model level you have a calculation group where you can create any number one of them in here so the one that we're discussing is my time intelligence calculation group the column for calculation items contains all of them and that pattern in here is just the month to date which is calculating the selected measure for dates month to date dates quarter to date and dates year to date so that's the first one that I wanted to show which is again one of the more common patterns that you will see and just a reminder that it can be used within a visual it can be used as a slicer or can be used in the filters pane now another common pattern that I like to see with calculation groups is a date selection so in this case what I have is a calculation group again being used in my columns section over here on The Matrix table but we can see a calculation being changed between my delivery date my order date or my ship date any one of these three is calculating based off of the inactive relationships that I have over here in my model between my sales table and three inactive relationships that are being called upon and taking a look over at the date selection calculation items we have the selected measure with used relationships being called upon between my order date my ship date and my delivery date all contained within here here so that common pattern number two is activating different relationships between two tables often a calendar table now the third and final pattern that I would like to show for calculation groups is the currency pattern so we have a couple of examples here and I'll walk through each in turn the visual over here on the far left is leveraging a calculation group now you might notice it's not actually being used in the visual below here but if I open up the filters pane I do have a filters applied to this visual where my calc item is the currency symbol if I clear this what I'll get is the same US currency symbol being applied to all of these now the calculation group itself what that is doing is it's applying a dynamic format string which is automatically changing the symbol depending on the country of origin so let's briefly look at the formula for this so coming over here under currency format my calculation items for currency symbols I'm not actually impacting the item itself so it's just simply returning the selected measure but under format I have the custom format string supplied into here so I'm looking to see if there's a single country being supplied I'm grabbing the the single country symbol and if and if country name is not blank and this will only return not blank if there is a single representation of a country coming from within the visual it will then return the currency symbol concatenated with the format otherwise return the original format now that in this visual if we come back on this visual here each one of these rows represents a single country because of the filter context being propagated from the visual so that's how it's able to change between us and China and Germany and all the others the currency symbol that represents each one of those now I did also want to see how I could leverage this with a previous video where I actually used autoscaling as well so I actually have in here a calculation for autof formatting and this is something that I built in a previous video I recommend checking out by the way my related content page that autoscaling video link will be in here but inside of this I'm leveraging this sales Auto format which is my sales order date value but I have a fancy format calculation in here in the dynamic format string which essentially scales the formatting but keeps the number as a number data type and what I wanted to get is the nice scaled values you can see here between millions or thousands or anything else depending on what the levels are now specifically in here I did find that if I attempted to use that individual with a calculation group it didn't render correctly so if if I was going to use the sales format in here instead it won't actually do the scaling it's overwritten by the calc group item so instead what I needed to do is I actually took this calculation format logic and added it directly in to the actual Dynamic formatting in here so you can't combine the two of them you cannot do formatting from a cal group and dynamic formatting in a measure the C group will overwrite that but in the sales format if I come up to the format St in here and I open this up I basically brought in that original one and then combined it with my Dynamic format logic built into here so the two of them together will actually concatenate those with a currency symbol being provided and essentially create that string so I'm able to do that at the measure level so you could either do that at the C group level you could do that at the measure level but there's a bit of overlap in terms of where to apply the dynamic formatting strings but you do have options for those and then last but not least I just wanted to show over on the right that if you have a visual that does not actually have context for a single country it will not actually return those so your logic can be set up in a way where even if this is applied it won't actually impact the values unless those conditions are met which in our case coming back one more time this has to have a single value in the visual being represented from the filter context for it to actually work but it's a great way to not only apply any type of logic change in the filtering which we saw between our time intelligence calculations and our date selection but count groups can also be a great way to apply Dynamic formatting but it also once again represents a way for you to see that you could also apply Dynamic formatting even at the individual measure level so you have many different options around where to apply these and like I mentioned at the beginning calculation groups are very robust I wanted to mention three highlights that really talked about how to change the calculations themselves with c groups and also how to change the formatting as a Cornerstone of three primary methods which I commonly see patterns of how these are used but hopefully you found some of this useful maybe something you can apply to your own reports as always if you have any comment suggestions about this video or any future video Drop That Into the comment section down below don't forget to check out some of my related videos on that Autos scale down in the description check out some of my related videos here on the upper left and as always don't forget to like comment and subscribe to help the channel grow and otherwise I will see you all in my next video

Channel: Havens Consulting

Views: 1,831

Keywords: Power BI, PowerBI, PBI, DAX, Data Modeling, Visualizations, Tips & Tricks, PowerPivot, Power Pivot, Power Platform, Power Query, Power BI for Beginners, Power BI Training, Power BI Desktop, Power BI Best Practices, Power BI Relationships, Power BI Dashboard, Power BI Tutorial, Power BI DAX, Power Query Excel, Power BI Versus Excel, Power Query Tutorial, Power Query Functions, Power Query Parameters, Power Query Editor, Drill Through, Power BI Service, Fabric, Semantic Model

Id: q5Ri_8BjCl4

Length: 8min 25sec (505 seconds)

Published: Tue Dec 26 2023