DAX REVOLUTION!? Visual Calculations in Power BI

have you always struggled writing Dex mans in powerbi well maybe those times are over because now we have visual level calculations now let's see if this is the start of the Dex Revolution a lot of people they are very excited when they start working with powerbi when they see all of the possibilities but that excitement quickly disappears when they have to write their first measure because you need to know all different kinds of Concepts like filter context roll context context transition and that makes it maybe a little bit too difficult for people that might not be using it on a daily basis but that is exactly where visual level calculations come in TX is going to be much easier now let's see how it works if you want to follow along make sure that you're on the February 24 version or later and that you turn it on on the preview option okay so let's try it out let's select over here this Matrix visual that I prepared that shows the sales actuals over time and now I want to add a calculation to it so I go here to the Home tab and there in the calculations group there we have new calculation all right now what happens is that a new window pops up at the top we have our Visual and at the bottom there we can add our visual level calculation now if this is the first time that you do it then probably you want to make use of some of the templates that are given now if you click down on the FX button you get this overview with different types of calculations now here I see division in four groups first of all running sum so they running calculations so which accumulates that over time for running sum moving average is the second group so the rolling calculations then we have the next 3% of parent grand total and children those calculations involve a different level of detail and then the last four those are comparisons versus previous next versus last okay now let's try them out and let's see what kind of new functions we have here as well so let's try with one of the easy eest ones so versus previous now that gives us a nice little template for this type of calculation now in front of the equal sign that is the name of this measure just like with normal measures and then here we have a reference to a value field now that value field needs to be in the visual if it's in your data model but not in the visual you cannot refer to it okay so I can replace this then with sales actual now always helps to go and have the square brackets open then here we have that measure all right perfect so here we have a new function previous that is then the previous item within the partition now it will become clear what that means in a second so over here I can also get rid of field and then replace that with sales actual and Tada that's it it is easy as that we have now the difference period over period so if you look here at the Top If you go from q1 to Q2 that is min -4.6 million and if you look at the year level there we have the difference between the years so it recognize ah here we are in the year partition and then we are comparing the 2019 value versus the 2018 value the previous value is minus 40 million all right now let's go back to that calculation for a second now at any point when you want to add the calculation you can always go here to the builds panel click down on that Arrow added calculation that brings back that formula all right now over here the interesting part is this function previous okay now I'm going to copy it click here on the check mark and then add new column all right and I'm just going to call this one previous period value okay press enter and you see that Returns the value from the previous period so when we are in Q2 it Returns q1 value when we are here in 2019 it Returns the value from 2018 so the question is is this really that much easier well let's have a quick look I'm going to click on back to the report and then open here the data panel now I prepared already a measure that shows the alternative well calculate sales actual total then I have to go one period back with the DAT ad function and you see these are not straightforward functions for at least somebody that's just starting off with powerbi all right and now well previous it's much more intuitive I would say right so de suddenly becomes much more understandable when we can do these visual level calculations all right now let's go back so I go here to the buildt panel I want to go back to these visual calculations editing menu so just click on one of them edit calculation and that brings you back okay now if I want to add another calculation then just make sure that you click first on the check mark then template now here we have versus next first and last there no surprises there right if I would say versus first then we have ah the first function and if I would have last then we have the last function so that's the last item within that partition all right now that is the first group of calculations now let me just get rid of this one now what you might have noticed though is that the formatting is not the same as the formatting that I have applied to sales actual total right if I go over here to my data panel select sales actual total and then go to measure tools you see H there is the formatting string that is applied which means okay show the values in millions but then I do this visual level calculation doesn't show it in millions hm it's a little bit annoying so the number formatting doesn't get picked up from that measure that I base it on and there's no way to set up the number formatting yet but I guess well with the next update or so they will probably add this right now for the time being though if you want to use these visual calculations already that what you can do is just addit one of these calculations so go back and then here we can just wrap it for now inside of a format function around right and then in between quotation marks there we can then use that formatting string all right so I'm just going to use the same formatting string as I have for sales actual total and that fixes it for now now another thing that you might want to know is that you can still use normal Dex functions also here right so let me first get rid of that previous period value I don't need it anymore okay and here I only want to show versus previous when there is a previous value right so over here here my data set starts in 2018 so here the first q1 January well gets compared to well zero right so and therefore that value is so big here so if I want to edit it let me just edit the calculation then then we can extend this a little bit further right we can still say if and then check if the sales actual total so square bracket open Sayes actual total if this one is not blank now how do I do this not not is blank okay so let's close these brackets there if this is the case then I want to calculate the difference okay and otherwise I just want to show nothing so I leave it empty and it still shows because I made a mistake let me just quickly go back and of course here I want to check if previous sales actual total is blank so I wrap this now in a previous function just like this and that gets rid of that January value that we have there and also the total value for q1 there and you see in the visual itself also here we have 2018 and q1 value they disappear all right good so now it's time to explore the second group of calculations which is the running calculations okay so I'm going to go over here to the templat running sum let's just use that one and you see also here the function name is not surprising running sum and we can just refer them to the fields that we want to accumulate that we want to have the running total of now in this case that is then the sales actual total okay and again without having to create any complicated decks we have now an accumulating sum now what about a running average because here the template gives me running sum but maybe there's also a running average calculation right is something that I would expect there to be so I'm going to add another measure so I'm going to go over here and let's call this one then running average and before this the fun fun that we used was running sum right calculator running Sum along the specified axis of the visual calculation data grid okay now I'm going to replace some with average H doesn't exist yet you see or running Max running Min calculations or functions that you expect there to be they're not there yet but I guess they probably will add them at some point okay so that's a little bit too bad for now okay now let's then go on and explore the third group of calculations which is the moving calculations the rolling calculations like moving average okay so I'm going to click there on next and again let's go to the templates now from the templates let's click on moving average and over here we have again the name moving average is fine for now and here we have the reference to the fields now the field in our case is going to be the sales actuals total and window size so I want to go two periods back right so over here two and include the current period okay now anytime when you want see what this function does this new function just go there control space and then here we have the description if I zoom out you see it a little bit better so calculat a moving average along the specified axis of the visual calculation data grit okay sounds good and let's press enter now the moving average is not a simple calculation usually but here you see with just the moving average function we seem to have calculated the moving average okay now let's go back to the report Bo so that we can see a little bit clearer so over here on the left hand side there we have sales actual here we have the moving average let me just move the two next to one another and now you see here we have the three period so not the three month moving average but three period moving average so here in Q3 this is taking the average over Q3 2 and one if we are in q1 well the value is the same because there are no previous periods and in Q2 it's kind of incomplete right now if you want to exclude these two where you do not have three periods then you would have to adjust that calculation further right and that would add again a little bit of complexity all right now for not showing the versus previous and running some calculations you can just hide them or remove them right so if we go back over here I can added the calculation you see there's this little hide icon and I can just hide them or remove them if I don't need them okay and that is also for that original measure so that original sales actuals well you don't have to show okay now let's go back to the report let's see how this looks like in a line chart because usually you would see that moving average in the line chart now if I go to the month level and maybe let's take the quarters out for a second that looks quite nice right now you see that moving average movens the line so that you don't see these fluctuations so much from month to month now it might be though that you want to see maybe four five six months moving average that means you would have to go back at of the calculate and then here instead of two you might want to have a five so that you have this six Monon moving average and if then go back you see an even smoother line now if you're wondering what's up there on the y-axis why does it show 0 million well that is just formatting let me just go here to Y axis values and then set the display units To None because the formatting is controlled over the measure and you see there we have it's showing in millions these two commas there before the decimal separator so you see for the moving average pretty easy to set up now also here the comparison would be a long measure now although it must be said there is also a way to use Quick measures right to create that moving average if I go here to Quick measure select calculation and then here we have time intelligence rolling average there we can say the base value should be sales actual total then the date should be from them date and then the date column all right ah but then you get an error because you have to provide a date Hier key so you run into issues sometimes with these quick measures but let's say you are able to create a quick measure then that quick measure would look like this which is not very understandable for somebody that does not have so much experience with writing decks in BBI so yes it's easy to create but H the code might be a little bit intimidating and throw you off when you want to make adjustments okay so let's go back now another thing that needs to be noted is that when you have a moving average then the x-axis is always categorical right if I go to formatting options X AIS you see cannot switch to a continuous data AIS okay now of course it can make it look better by turning concatenated labels off and the title I don't need those can turn them off okay so now another thing that would be nice is if we could control that moving average right so if the user could just say how many months the moving average should be calculated over or how many periods now to be able to do that we need a numeric parameter so if we go to modeling new parameters the meric range now over here let's call this one number of periods and then the minimum let's say if we were to have it for months then you want to start with one month back or zero months back maybe till the maximum 11 and because if you include the current one plus the 11 previous one you have exactly a year increments one by default 3 months or two months back at the slicer okay that gives us a nice little slicer let's put it right next to a visual let's put it to two and now in that visual level calculation I want to refer to the selected value now normally you would have the selected value being returned by a measure right so over here we have periods value that Returns the selected value and that I want to refer to now the problem is that with visual level calculations we cannot refer to measures that are outside of the visual all right so maybe if we add that bu value to our visual okay now it messes up the visual bit we have there that line at the bottom but I don't care okay then I go back to the visual level calculation so here in the build panel go to the visual level calculation addit the calculation then instead of five I can refer to the number of period value right so let's see if I now the square bracket open Period value there it is press enter and I breakes cannot display the visual Matrix the visual count calculation moving average has a syntax or semantic error blah blah blah and the moving average expects a constant as argument number two ah and it's not a constant so therefore a reference to a measure there it's not going to work even if that measure is in the visualization H that's a bit of a bity so hopefully that's going to change so let's edit the calculation and let's just hardcoded done for now two okay that also brings up the question what about field parameters can we use field parameters and let's quickly check if that's possible so I'm going to go to modeling new parameter fields and let's keep it s simple so this is going to be a period switch then over here I want to have the from the dates I want to have the year and the month okay and let's take that slicer and put it instead of the other one because we don't need that one anymore okay then we can go back to a vis visualization where we remove the selected value from that numeric parameter okay but now I want to have the ability to switch between quarter uh the year and the month okay now I'm going to go over here and instead of having here the can high key I'm going to get rid of it and now of course this will break yeah because this measure doesn't have to breakdown anymore okay so clicking on the xaxis period switch and let's add this then over there and when I add it you say that field parameter doesn't work anymore even if I switch it to the month it just says year month so you see a field parameter doesn't show anymore as a field parameter okay so that's probably then not working so I have to well go back to our visualization and replace it again with what we had before so year month now I have also tried to First add the field parameter and then do a visual level calculation also doesn't work okay then it would just simply not let you okay so numeric parameters field parameters not an option just yet but I probably it will be there at some point all right so here we have a moving average which is nice and probably the most common rolling calculation that you want to do but is it also possible to do maybe a moving sum or moving total or moving Max and moving men well let's go back over here to that visual level calculation now over here let's just see if we can replace average with the moving sum no moving moving Max men no only moving average so for the time being it's only moving average unfortunately because having moving some Maxim opens up possibilities now at this point some of you are probably thinking these type of calculations don't I know them from somewhere yeah these are kind of like table calculations in tblo right so let me switch quickly to tblo so that you can make a comparison so here I am in tblo and I just created visualization that shows the sales development over time and I want to have the moving average okay now what you would do here would be the following you just go to that field some sales and then you have table calculations oh running total difference percent of total sounds familiar right now if I then go here to moving average then we have a moving average you see it's moving the line now if I want to well adjust a little bit further then I can always go back at a table calculation and you see here we have the type of calculation table calculation and we can say how many previous periods right so I can moo in it further Etc okay now that is basically what we now have in powerbi as well but the interface here in tblo is still a little bit nicer but again it's just a preview option b and let's see where we are in a couple of months from now and before I forget here you do have the option to for example change that summary aggregation to sum minimum maximum and it's the same for the running calculation running sum min max Max average and that opens many possibilities all right now let's go back to Power behind now there's still a fourth group of table calculations I mean visual level calculations so let's go back over here to our example now over here I have also Matrix but I just change the breakdown to product category all right and now over here I want to add a new calculation from that fourth group right so that is your percent of parent grand total and average of children now if we say percent of parent and here you see it gives us nicely the template we can divide the field now what field do we have here we have sales actual total and a new function collapse all right now collapse what now here again the field sales actual and what do we have in the access we have the subcategory okay super intuitive and easy to write now here you see we have now the percent of subtotal perfect and what about the percent of overall total of grand the column total now let's try that one as well we have over here percent of grand total which gives us almost the same so we have here the reference to the field sales actual total but instead of collapse we have collapse all okay so one of these new functions that we need to learn now let me also update the arguments so here the access though is not subcategory but in this case you have to say row okay so that gives us then the percentage of the overall grand total okay so the important function here is collapse or collapse all right so if we add a new calculation where we can uh dive a little bit deeper into this function so here collapse all right then we have the expression so here we have sales actual and what we have on the axis well we have the subcategory name okay let's close the brackets so you see that Returns the summer sales at the category level it excludes the subcategory excludes so in tblo that would be exclude function all right now just like exclude there is then also include and here that is called expand okay so let's try that variation as well I'm going to go here to FX and here we have the average of children okay so expand now if you want to see the description right control space click there on that little arrow retrieves a context with other levels of detail compared to the current context if an expression is provided returns its value in the new context allowing for navigation and high keys and calculation at a more detailed level okay so here that basically means that we can say expand right now let's say we want to have the average sales actual and then here accesses subcategory okay now what is this going to do let's have a look okay now this basically sums of the sales at the subcategory level and then of those values takes the average okay now to see the little bit clear let's go back to the report all right and I take that last visual level calculation and I just drag it up so that's the second column over here okay so here we see the sum of sales here we see the average now the average is the same as the sales actual when we are at the subcategory level but where it gets different is there at the category level because what this expand does it basically expands to the subcategory level gets these three values some of actuals and over these three values takes the average right and that is the 2.2 that you see over there and over here of these four values it's 6.6 and even if I go to the category level you see it first calculates the sum of sales at the subcategory level and if those values calculate the average so you see these type of functions are like include and exclude in Tableau so people that are switching now from tblo to powerbi they probably will be happy all right now let's clean up this visualization a bit and answer the question can we actually use these visual level calculations also for conditional formatting let's say now I'm going to get rid of all of them all right now let's see if we can use these visual level calculations for conditional formatting or other visual elements that we can uh control normally with measures okay so I'm going to click on new calculation let's just call this one CF to keep it simple I want to see if the C actual is bigger than a certain amount let's go for let's say 10 million now you see that returns true or false now if you want to you can also return the color straight away right so you can say if this is true then I want to have the color green and otherwise I want to have the color red okay so it is working then I can go back here to the report and then we can go here to formatting and then we have conditional formatting in the sell elements sales actual total now here you see the other one is also there right so I can also select CF background color all right sounds good clicking on Ax and now I want to have field value now that visual level calculation doesn't bub up over here so I cannot use it for conditional formatting and that's the same for reference lines that we potentially could add so it would be nice to have a way to reuse these visual level calculations for controlling conditional formatting reference lines Etc okay okay so over here unfortunately not possible all right and what about custom visuals can these visual level calculations also be used for custom visuals well let's try it out and I'm just going to get rid there of this conditional formatting then also here data and then get rid of that visual level calculation and now I'm going to click you on get more visuals and let's see what happens when we turn this visualization into a custom visual can we still apply visual level calculations okay so over here let's just look for Matrix maybe that one from zebbi all right so here we have that table visual from zbi let's turn the Matrix into that one all right perfect now I want to see the forecast as well so let's add it here first two values perfect and now we go to home new calculation Is Not Great out and I want to calculate difference so difference between the forecast and see sales actual maybe the other way around so over here we have sales actual minus the forecast all right now here you see the difference is correct we added it now we just have to make sure that for this visualization it is in the right place which I believe is not here but on forecast so we can just drag and drop it over there okay perfect nice so the conclusion yes visual level calculations can be used for custom visuals and what about all normal native visuals so so so far we just have seen the line chart and the table or Matrix right but are there other visualizations for which it might not work well let's go over here to the build panel let me just make this a little bit wider and here you see we have stack bar chart let's click on that one first okay now how the visualization looks like doesn't really matter you see new calculation is possible right it's not great out now just like this you can click on every single visualization and everything works up to the point where you get the line and stack column chart and you see then it will say ah it's not supported okay so not every visual is supported also when I would click on the map visual calculations would be removed so there are few for which you cannot do visual level calculations at least not just yet all right and what about calculation groups can we still use calculation groups when we have visual level calculations well let's give that also a try I'm going to get rid of of the sales forecast all right okay then I'm going to switch back to a matrix I don't need to have the difference so I click that one away okay now let's bring back the forecast again so over here I'm going to add sales forecast total and then right next to this Matrix we're going to have a slicer okay and on that slicer we're going to add the calculation group okay which I've already set up so over here see time intelligence pretty straightforward and then if I click around here you see we can switch between different calculation types okay which are just measure patterns that get applied to these measures okay now let's add then the visual level calculation that calculat the difference okay so I'm going to move this a little bit to the right let's make it a little bit bigger and then click on new calculation again and then here we have the difference and this is going to be equal to we have sales actual minus the sales forecast so so far no problem it shows me exactly the correct difference just with it different number formatting then we can go back to the report everything is still fine now if I switch to current here also works and what about year to date also works okay so calculation groups still work even with visual level calculations and then another question that you might have is do visual level calculations still work when you're not in mode but in direct query well over here I have a different powerbi file and you see here I'm in direct query mode so you see on the left hand side there's no table view right and here we have a visual level calculation that calculates the moving average right so you see over there everything still looks fine also here the button is not great out we can do all of the visual level calculations just like an inut mod all right and then the very last question for this video and that is what about speed are visual level calculations maybe much quicker or slower than normal measures well over here I've set up an example where we have the visual level calculations for the moving average that one over there and over here one visual that has the sales amount moving average but then using a measure that was generated using quick measures so that looks like this one now to see how long the Dax query runs what we could do is go here to view and then just make sure that the f analyzes turned on and then here on the right hand side you might still have to click on it as well and then we can start recording now if we then click on refresh visuals we see how long each visualization runs for now it's is about the same however there's a around 1 millisecond difference so we can ignore that and if we then open one up then we know okay this one is for the quick measure the Dex query run for 28 milliseconds okay so it's not long anyways right so and if I go to the first one One open that one up there the Dex query for the visual level calculation oh took quite a bit longer 54 milliseconds okay now let's try this again so I'm going to clear this start recording refresh the visuals let's see if the result is more or less the same so let's open it up and again the one for the visual level calculation is quite some milliseconds above the other one again you know like it's milliseconds that we're talking about but if we have maybe more complexity in the type of measures the difference could be big potentially all right now let's give this one more try I'm going to go here to the next sheet where I have the following example here on the left hand side we have a visual level calculation here on the right hand side there we have the percent of total a measure that I wrote just a normal measure now then start recording let's refresh the visuals okay then I see over here clear difference however which one is which so the first one that is the one here on the right Dex quering run for 25 seconds then I go here to the second one all right that's the one with the visual level calculation 47 milliseconds so almost twice as long all right so here the question is what is more important the convenience of writing these measures using visual level calculations or the performance Improvement that you get of writing normal measures well over here we're talking about a few milliseconds so definitely I would go for the visual level calculations in that case but if this becomes 100 200 300 milliseconds well then becomes a good argument to still use normal measures okay so this of course needs a little bit more of proper testing all right so am I excited about visual level calculations vary because Dax is just a big hurdle that a lot of powerbi users never overcome and visual level calculations can change this now of course powerbi probably is looking at what dou did there with the table calculations but it makes sense it is a little bit easier than normal decks however a few improvements are still necessary so the limitations as I see them at the moment is that well number formatting that's a big one we have no possibility to use field parameters or conditional formatting there's no way to reuse the visual level calculations yet right so we cannot store them in a model in table you can just drag and drop them to your model I guess and that's probably also coming to Barbi at some point then we are still a little bit limited to what type of calculations we can do right we have running some but not average some or Max sum and it's the same for the moving average we don't have a moving sum or a moving Max right so that would still be nice and the visual level calculations they cannot be used yet in a combo chart which is quite a big one as well so let me know what do you think do you think that visual level calculations is going to start a whole new Dex Revolution yes or no let me know in the comment section below now I hope that this video was helpful and if you just want to learn normal decks the old school way then just check out this video over here which gives you a good starting point thank you for watching and I hope to see you in the next video

Channel: How to Power BI

Views: 63,020

Keywords: powerbi, power bi, datatraining, datatraining.io, data training, bas dohmen, dax, dax tutorial, visual level calculations, vizual level calculations, running sum, moving average, window calculations, percent of total, dax easy, dax for beginners, february 2024 powerbi, release

Id: ZWooHoshXv4

Length: 33min 15sec (1995 seconds)

Published: Wed Feb 21 2024