Budgeting with Power BI

ciao friends in this session i want to work with you on a budgeting solution using power bi here is the scenario contoso already has a power bi model that is useful in order to analyze the sales we are at the end of 2009 and we want to integrate inside the existing power bi model a budgeting budgeting information so we have an excel file containing the figures for the next year 2010 and we want to merge this information inside the data warehouse in order to do further processing we will start from the raw data and we will integrate everything inside power bi in the process of doing all these steps we will face several issues several problems that we will need to solve the final takeaway of the session is not how to compute a complete budgeting solution that is up to you because when it comes to budgeting each company is a story by itself instead i want to show you how you can leverage dax in order to build complex solutions and obtain great results follow me the start is this excel file as i said we have contoso contoso has its own power bi model but they want to integrate this budget this budgeting information now the budget information contains uh not very detailed information it contains just rough figures so we say that for in china for adam we have three different scenarios low medium and high low says that we sell 171 thousand dollars medium is a bit higher and the high is definitely much higher and the granularity of the information is at the country region and at the brand so we have three countries china germany and united states and the set of different brands so as you see we do not have information for each individual product we do not have information about time we don't know when sales will happen and we don't have very detailed information we just have a rough figure we want to integrate this information in power api now i already created the table in power bi you see that we have this table containing country brand the only thing that i already did is that i am pivot the scenario so instead of having it in the columns we now have it on the rosa so china a datum the scenario and the budget value we can use this information to slice and dice exactly as i'm doing here so i created the already a matrix that is showing counter region the brand and the slicer let me select the different values for the budget the problem appears as soon as i start to integrate sales into this model because the model already contains information about sales so if i want to compare the budget with the sales in the previous year i have a measure sales in 2009 that computes the sales for 2009 but if i put this measure in my matrix you'll see that the numbers are not exciting it is showing the same value everywhere the reason is that i created the table budget but i have not created any relationship with the remaining tables in the model the model contains sales customer data and product but there is no relationship between budget and data there is no relationship between budget and customer and same with product that's why when i slide when i try to slice by country region from the budget table and i look at sales i have i have always the same number and the scenario is not going to be different is if instead of using columns from the budget table i get rid of the brand and the count region i can use the counter region from the customer now the scenario is different because country region from customer slices sales correctly but it does not slice in any way the budget now budget shows always the same value so the first problem that we need to solve is to build the set of relationships a correct set of relationship between the budget table and the remaining tables in the model we have several options of doing that and we will see them one by one before moving further let's look at the tables that we need to link we have customer we have data and we have product we will use three different techniques for the three tables we have as you have seen that we cannot slice by country we cannot slice by product brand the brand is one of the columns in the product table and if i try to slice by product brand i have the same scenario and again i cannot slice by month month slices sales correctly but it does not slice up the sum of budget if we look at more details when it comes to customer we have actually the count region column counter region is present in budget and it's also present here in customer in a similar way for product we have the brand column in the budget table and we have the brand in the product table so we will be able to use these columns in order to build the relationship but when it comes to the date if i want to slice by month then i'm definitely in trouble because data contains the month number and the month budget has no information at all about when sales happened if we look again at the budget data you see that we do not have any information about the time we need to solve the three scenarios one by one and we start by solving the scenario with the month just because it's the first one that we have on the screen now think about that how can i create a relationship between a budget table that does not contain any information about data and the data what i need to do is to provide information about dates in the budget table i can put the year i can i have multiple options the option that i'm going to choose is to increase the number of rows in the budget table so instead of having this structure where we only have count region and brand i want to add one column to the table that contains the month number by increasing the number of columns the value of 231 that is the budget high for a datum in china will become 12 different rows so i'm increasing the number of rows in the table if i increase the number of rows i need to guarantee that the total so the sum of the budget is still the same so i will increase the number of rows but i need to divide the value of the budget by some number in order to obtain still the same total even though it will be split month by month to do that i have several options i could just divide the total by 12. so i have 12 000 let's say for 2010 i divided by 12 and i put 1 000 for each month but by doing that i'm not actually computing correct values or values that have every a good meaning because i'm assuming that sales will happen every month the same it's not the case contoso does not have such a precise division of sales what i want to do instead is follow the pattern of sales of the previous year and slice sales by month following the same pattern that's why i built a report sales distribution that shows the distribution of sales over time now numbers are hard to read but we can zoom in a bit you see that i'm what i'm projecting here is by country and by brand by month the value of sales in 2009 and you can see that there is a clear pattern that depends on the country and on the brand if you look at china for example in january they sell something in february looks like they are not buying anything at all in march they start buying again and in april they start buying as if there were no tomorrow then in may goes back to a regular value of sales and in june sales start again but this is for china if we look at germany things are different in germany sales are much more regular in the first month and still we have this peak in april if we look at united states again a different pattern so sales are completely different depending on the brand and depending on the count region if i want to use this information to allocate sales by month what i need to do is transform this that is actually now a report into a table that contains the percentage of sales of every month and use it to multiply the value of the budget so i create a table that says well in china in january for a datum you need to allocate three percent of the total sales of china adatum to do that i need to write a query and then create a table so the first step is to take this report that is slicing by counter region brand counter region brand and month and transform it into a calculated table that contains the percentage i need to write quite a lot of dax code that's why it's now time to open that studio and start writing some code so let me open dac studio move it there and we have that studio ready here we also make some space because the code will be kind of long so what we need to do is group by group by sales by counter region and brand so we start writing evaluate we want to summarize sales by product brand product brand and by customer i don't need that country region i can start running this first query let's zoom in a bit and this returns the different brands and the different counter region then i need to add the sales amount oh i also need the month number so i want the brand the counter region and i also want the date month number now i have contoso united states the month number i need to add sales to it so we use add columns around it and we create a new column sales amount that computes the value of sales amount run the query and now we have contoso united states in january this is the value of the sales amount it's always a good practice to check that the numbers we are looking at are actually correct so we can go let's take a contoso united states january look here and we have a contoso united states january it's 3.95 so the numbers we have computed there which is 1.4 million is wrong the reason is i forgot to uh compute the sales for only 2009 this is the sales amount over all time so because the entire calculation need to be for 2009 i add the filter say that the date calendar year need to be equal 2009 run the query again and we have 395 which is the expected result now this is the sales amount so basically what i did so far is recreating the same report that we had in the matrix but i am not interested in the sales amount i want the sales amount and i also want the grand total of sales amount which is the number here so for united states contoso right now i'm computing this value but i want to compute this value 7268 because i want to divide 395 by the total to transform that into a percentage so we need to add a new column here which is the yearly sales yearly sales and to compute the yearly sales what i need to do is get rid of the filter on the month keep the filter on the brand and count region so to compute the total sales for all the months that is where calculate is useful that will be calculate sales amount and we'll remove the filter from the data mount number if i did it correctly now we have sales amount and yearly sale 7268 which is the correct number let's check it that is 7268 so we have the correct value and we can move further if you think about that i'm not interested at all neither in the sales amount nor in the yearly sales these two numbers are only useful in order to divide one by the other because i'm interested in the percentage of contoso united states january over all over the total so instead of computing the sales amount i can compute the percentage by dividing sales amount by the value of yearly sales now i no longer have the values i can run the query that becomes a percentage and you see that it is telling me that contoso united states in january should allocate 5.44 of the yearly sales that is the first number so now we have a percentage what we need to do is multiply this percentage for the budget of contoso united states that contains the value of the yearly budget this requires a bit of reasoning because if we look at the budget data in order to obtain the value for contoso united states uh let me sort by country region or actually by brand is probably easier we search for contoso united states you see controls the united states allocates six millions in the high scenario 4.5 million in the low scenario and 5.3 millions in the medium scenario so i have three different values for contoso united states in order to obtain one value only i need to provide the counter region the brand and the scenario at that point i can use a lookup value to grab the value and do the multiplication let's do that with the query i need to reduce the font a bit because now i have my percentage i need to have the budget information as i said we can use lookup value and we need to provide the counter region the brand and the scenario so i can use lookup value and search for the budget brand the font is a bit too small okay i want the value of the budget budget where the budget brand is equal to i can use product brand because add columns is iterating over a table containing the budget brand so the product brand and i can use the value right here and i can do the same for the budget counter region and that contains the customer counter region but if i want to obtain a number i also need to provide the value for the budget scenario let me just write low for now we will fix that later because i want to run the query and see that i actually have the value for contos united states low if i run it now you see that i have the brand the count region the percentage and the budget four five one zero that should be contoso united states low as we always do let's check it contoso united states low is four five one zero that is correct next step is that i do not want to hard code low inside my query i need it to be data driven so what i need to do is add to the result of this summarizer the values of the budget scenario and we do have three values for the budget scenario so i need to add it to the result of sunrise and cross join is the function that will help us do that i cross join this entire result with the values of budget counter region now that i have budget counter region in the row context i can replace this law with oh sorry that's not budget count region budget scenario okay i can take the budget scenario from here and use it here now i totally know that this code budget scenario equals budget scenario looks actually really weird the thing is that this is the column i'm searching into so i'm declaring the column that i searched into and this is the value i'm searching for so this budget scenario is evaluating the raw context created by add columns over the cross join and this is a column reference that declares the column that i'm searching into so despite being the very same code they are two different purposes and they are used in different ways if i did it correctly now i should have the scenario here you see i have a new column with the scenario the value for the budget is still correct but if we go for different scenarios we should find medium you see that we have different values for the budget and near the end we should also have high so now this query is computing the percentage and the budget as it happened earlier we are not interested neither in the percentage nor in the budget we want these two values only because we want to multiply them one by the other so instead of computing the but the percentage i can compute the allocated budget that simply multiplies one by the other and if i run the query now the result contains brand account region the month number the scenario and the value of the allocated budget which is the final goal that i wanted i can now take this code and create a table in the model but remember that the purpose the reason why we created this table was to build a relationship with the date table and we are still missing a date in the result the result that we have so far contains the month number but it does not contain a data that is not a big problem because we can link the budget to the first day of the month so we can create a date column where we use a date the year is 2010 because that is the year for which we are creating the budget then we take the month number for the month from the current row context and for the day we just use one the first day of the month if i run the query now you see that we have the brand count region month number we are not going to use the month number instead we are going to use the date column to create the relationship i can now copy these entire code go back to power bi and create a new table a new table we call it allocated budget and i copy the entire piece of code so this is the same expression i wrote in the backstudio just hit enter we check that the table is indeed there we have the allocated budget table and you see the same content that we had the index studio is now available in power bi next step is to build a relationship i need to take the allocated budget i can put budget away because i'm not going to use it anymore and build the relationship between date and date unfortunately it does not work and i have a circular dependency error now this is nothing to be scared of circular dependencies typically happens whenever you reference the blank row inside the expression you write we have a video that describes that we have articles so if you are not familiar with the concept of blank row you can go there and take a look what we need to do is go in our code and remove any reference from the blank row so if we find all we need to get rid of it if we find values we need to get rid of it here we're using values of budget scenario we need to use distinct of budget scenario or actually we could use all know blank row which is even better because there is no filter so the two would return the same value here we're using remove filters so that is not a problem look at value is not a problem we still have a problem with this calculate remember that filter arguments of calculator are actually expanded this becomes a filter all date calendar year where calendar year equals 2009 and now it is more evident the presence of all we never want all we always want to use all no blank row in order to get rid of any dependency from the blank row and if i did everything correctly now i can copy this code go back to power bi replace the code with the new version the content is the very same but if i did everything correctly now you see i can build the relationship between date and the allocated budget table doing it this way now let me get rid of it i can go in my report and start to make it work slightly better let me gain some space now i need to do some work because right now the columns here are using the budget table and instead i need to use the allocated budget table so on the slicer instead of having the scenario by budget i need to put the scenario of the allocated budget and here instead of having sum of budget i need to put the allocated budget value sum of budget can disappear now i have the month coming from the date i have sales and i have the allocated budget that changes depending on the scenario so this is already a first goal by changing the granularity of the table by increasing the number of rows in the table we have been able to build a relationship between the budget and the date table that is a first technique that you can use we have done everything in dax you can use a power query you can use whatever tool but increasing the number of rows is always a good practice if you need to allocate values we had to find a good weight in order to allocate values the right way and not just divide by 12 but we have other options to build this relationship that we are going to use for the next tables because we have a similar problem we have solved for data we have a similar problem with customer and with product let's focus on customer how can i build a relationship between the new table allocated budget and customer that needs to be based on the counter region counter religion is a column both in customer and in the allocated budget table but it is not a key in the customer table so if i want to build a relationship one option is to create a table here so i will be an intermediate table here that contains the values of the counter region so count region will be a key for that table and i will use it to slice both customer and allocated budget creating this table is much simpler because it only needs to contain the distinct values of the counter region so we don't need that studio to do that we can just go there and build stride a table let's call little country region i can use well we can go for all no blank row of customer count region and that returns the different values of customer account region if we want to really be precise we cannot just use customer counter region we need to merge it with the values of country region coming from the allocated budget table so that if a country for whatever reason is present in one table and nothing the other one appears in the result so i can union these with all no blank row of uh i'll we can use budget counter region now the problem of doing that is that now you see country regionals duplicated that is totally expected because union does a union all but i can run a distinct later in order to get rid of duplicates and now i have my table containing the different country region either coming from budget or coming from the customer table the table is here and i can now use this table to build the relationship so customer count region goes to country region and the allocated budget count region goes to counter region now country region because it's on the one side of relationship with both customer and allocated budget is able to slice both customer and allocated budget let's give that a try i need to remove the month and instead i take the counter region from counter region drop it here and you see that it is licensed by counter religion and i can see sales and budget correctly sliced by counter region the only thing i need to pay attention to is that i need to use the counter region from the counter region table if i use the customer counter region which i have here customer count region slices sales but it does not slice the budget and the reason is customer counter region is here a filter from customer will not reach count region therefore it will not reach budget if i use a column from this table i will be able to slice budget but if i use a column from customers i will not if i want to use a column from customer i could do a trick which is dangerous but in this case is useful that is enabled by directional cross filter on this relationship only if i enable bi-directional cross filter on this relationship now customer will filter counter region counter region will filter budget and if i go to the report now this counter region is the counter region coming from the customer but it is slicing budget correctly you always need to ask yourself before using bi-directional cross filter how safe this is and in this case this is not a problem because a customer filters counter region counter region filters budget and the chain of relationship ends here budget is not filtering anything else therefore bi-directional cross-filter in this specific case works just fine and this was a second technique so we have seen a first technique increasing the number of rows in the table we have seen a second technique creating an intermediate table in order to build the relationship there is actually a third technique that we can use to create the relationship between the budget and the other tables in the model which is relying on a weak relationship or as they call them today limited relationships this kind of relationship are able to link tables even though the column that you use to build the relationship is not a key allocated budget is not a key in the brain so brand is not a key in the allocated budget table brand is not a key in the product table but if i use a limited relationship i can build the relationship based on the brand and to do that it's enough to take the product brand from here drop it here and power bi gives me the option of creating the relationship with a big warning that says hey the relationship has cardinality many too many this should only be used if it is expected the nether columns contain unique values and that the significantly different behavior of many-to-many relationship is understood now this measure is basically a warning that is telling me hey if you know what you are doing go ahead but if you do not be aware that you might find a lot of troubles with that said whenever i see a warning yellow message that i do not completely understand don't know what you do but i typically just click on ok and go ahead so i just click on ok and the engine created the relationship unfortunately it created the relationship and it is not enabled because it tried to create a bi-directional relationship that would create ambiguity when you have limited relationships you need to choose the cross filter direction because when you have a one-to-many relationship the cross-filter direction is always from the one to the many side here you need to decide whether you want the budget to filter product or product to filter budget we know we want product to filter budget and if we make that we can now create the relationship and make it active because we avoid ambiguity now product is filtering budget through a relationship that you see has the many side on the product and has also the many side on the allocated budget table its behavior is nearly the same as the relationship between customer and counter region but i do not have to create the counter region table i can just rely on a limited relationship let's see whether it works let's get rid of counter region and instead we use the product brand where are you product brand here we are now i can slice by product brand i see sales in 2009 the allocated budget and everything looks like it's working fine indeed it is if we take the product brand and the customer counter region i don't find customer okay counter region before i can slice by counter region i can slice by brand and i can look at sales in 2009 and the budget side by side so by creating relationships i we have seen three different ways of creating relationships and by creating relationship we are now integrated the budget inside the models containing all the sales we need to go one step further and check that the numbers that we are computing are actually correct because we might have problems if we do not pay attention to how numbers are computed so let's see how correct they are if i slice by counter region it's fine if i slice by brand it's fine if i filter by different scenarios you see that the budget changes and again that is fine what happens if i go over that so if instead of limiting myself to use columns which are meaningful for the budget i use columns that go farther than that so for example i can add the color and i didn't want it on the columns i want it on the rows if i expand a datum you'll see that now we start to have a problem because the budget does not show different values for different colors this is a kind of expected because think about that the relationship between product and budget is a relationship at the granularity of the brand that means if i slice by brand the filter on the brand will go to the allocated budget but if i farted slice by color this is not affecting the relationship that still uses the granularity by brand it is similar to what we did with the data we linked with the 1st of january that is if we slice at the month level everything will work fine but if we go at the day level we will see incorrect results because all the all the budget will be allocated on the 1st of january here the budget is allocated at the brand level and not at the product level this doesn't seem like a big problem because i mean when you see the same number repeated again and again you know that something wrong is happening the engine is not able to compute the correct value with that said showing a number which is not correct or which is not sliced correctly is always a problem it's your responsibility as a dax developer to make sure that if you show a number this works correctly if the filter context has gone too far so you are filtering by color and your measure does not support filtering by color then it's your responsibility that of blanking out the measure providing an error message do whatever to inform the user that you cannot compute that number so what we need to do here is avoid repeating this number again and again and instead show a more meaningful number even because right now you see the numbers are all different but what happens if i remove the brand from here if brand is no longer part of the equation now you see that the numbers are all different which would make a user think that these numbers are indeed correct instead all these numbers are wrong there is not one of them that is correct the reason being the relationship is based on the brand when you filter by color the filter on the color results in a cross filter over the brand and the filter on the brand will go to the allocated budget so if color is red brand is showing all the brands that contain at least one product that is red that is red and the filter over the allocated budget will show the budget of any brand that contains at least one product that is red which is definitely the wrong value so we need to fix that and to fix that let's go back to the original report that also contained the brand we need to understand when the filter has gone too far so i want to show this value because i'm slicing by counter region i want to show this value because i'm slicing by brand i do not want to show this value because now i'm slicing also by color slicing by brand is supported slicing by color is not so you need to write code that understand when slicing has gone too far there are a lot of different ways you can achieve this goal one option is to use is filtered you can check is filtered on all the different columns in order to understand which columns are filtered and which columns are not this would work but it's incredibly boring to write code that the checks is filtered for all these different columns instead what we can do is leverage again dax and understand by the number of products visible in the current context whether we are slicing at a granularity that is still supported by the budget or we have gone too far let's see that with an example let me add to this report a simple calculation that is just the number of products number of products which is just account rows of product and then i add it to the report why is this number useful well this number is useful because it is telling me that when a datum is selected so when the filter count exists filtering a datum i should see 132 products if a datum is selected and i see only 14 products that means i'm not seeing in the current filter context the entire a datum set of products i'm seeing a subset of it and if i see a subset of eight atom this means that i cannot show the budget because i'm looking at a smaller number of products and the budget is related to the entire family of a datum products so i need to check that when adatum is selected i see 132 products and when adventure works is selected i say 192 and so on for contoso you need to be 710. what i need to do is build another measure that computes a 192 here for black for blue and for brown so that i will be able to compare the number of products with the number of products that i should see if i were slicing at the granularity that is supported by my budget so what i need to do is build a measure that computes 192 here this measure is not hard to compute we call it the number of products at the budget grain we use calculator we compute the number of products and then what we need to do is remove any filter from the product table and keep only the filter on the product brand we could use all except but all except would work for the product but it would not work for the customer the reason is the difference between using all except or all values we have a video that explains that so if you're not sure about the difference go look at the video read the article because it's important to understand when to use all except and when to use all values in this case what we need to do is remove any filter from the product and restore only the value of the product brand this computes the number of budget at the granularity supported by my budget and if i put it here you see that i have 132 when i am at the budget grain but i also have 132 everywhere else regardless of the filter that i'm placing and this is not tied in any way to the color i can get rid of the color from here let's get rid of the color which is here and instead use the category the numbers will be different but still i see 710 everywhere now category is a bit too large so we can get rid of category and put again the color so we see all the values together last step is i need to protect my budget information in order to avoid showing the value if the number of products visible is not the same number as the number of products at the budget granularity this is just a simple if statement but here i'm still using the implicit measure allocated budget you see that i just took allocated budget from here instead i need a measure so we can build a measure to add our if statement which we call a protected budget which checks if the number of products is identical to the number of products of budget grain then it shows the sum of the allocated budget otherwise by default it does not show anything we format it as a decimal number with commas now from here we can get rid of the allocated budget and instead we put the measure projected budget see what happens now i have the value of the budget when i can show the value and a blank otherwise it is extremely important that you pay attention to these details because you do not want i know i'm repeating myself but you do not want to show wrong values if the filter context is not what you would expect or what is needed for your formula to work you need to blank out the measure and say i don't know how to compute that number never show a round number that's a really bad practice we still need to do that for the customer too so we need two measures uh let's save some space let's get rid of number of products and number of products of budget grain we have the projected budget we create a new measure number of customers we just account rows of customer put it here we have five customers in china and 1791 in germany let me get rid of the color we don't need that anymore so five customers in china i need to build another measure we use in the same pattern that we used earlier number of customers at budget grain ie grain which uses calculator number of customers we remove the filters from the customer and we restore the filter at the correct granularity which is the customer counter region and now that i have the two measures i can go in my allocated budget protected budget and check that this is true and at the same time the number of customers is identical to the number of customers at budget grain by writing the code this way i'm sure that i'm protecting my code from both ex going to further in the bar in the car in the product and also in the country let's give that a try let me remove the brand and instead from the customer we add for example the education we expand germany and you see that predicted budget does not show anything because number of customers shows a value but number of customers are budget grain for germany shows still 1791 so these two numbers are not identical therefore the measure blanks out the value so so far we have created this report that is able to slice by counter region it slices by brand and it blanks out the measure if the granularity is not supported the question is can we do anything better than that we have been able to detect when our calculation might fail but can we actually do some calculations instead of blanking out the measure can we put some reasonable number if the user decides to browse further in the granularity actually turns out that we can do something better than that let me rebuild the original report i want to slice by counter region i want to slice by brand so i have china a datum and i also want to slice by color finally find the color now ask yourself what number should i show here i don't want to show the value of 171 because we know that would be wrong but what if i say well i don't know what the value should be but i can assume as we did with the days that the division the separation or the yeah the division by color the allocation by color might be the same that happened in 2009 and i have the sales in 2009 i know that one million of sales were of black products so around one-fifth of the entire sales amount was black so i can take this number divided by this number multiplied by that number and this computes a number that i can put there which is reasonable what i need to do is compute the sales in 2009 that i already have and the only number that i'm missing from the equation is this value computed here so i want a measure that computes the sales in 2009 at the budget granularity whatever the filter context is and the technique is the same that we used in the measure of number of customers of budget grain this time i will compute the sales amount remove any filter from product and from customer and restore the filter at the budget grain that will give me the denominator to use in the percentage that allocates the budget we can do that let me create a measure that we call sales 2009 at budget grain which is just calculate sales in 2009 we remove filters from customer and we restore the filter on the counter region then we remove the filter from product and we restore the filter on the product brand you see it's the same pattern we used earlier it's only that now i'm doing that on both tables both customers and product we format it as a decimal number put it here and it's just wrong while that let me see sales in 2009 at budget grain remove filter values of product brand that's kind of weird because i mean i wrote this code a ton of times and uh i know it works so that should be something different the code seems correct so to show something different let me see oh i see that here i'm using the brain column from the allocated budget table so the filtering is going the wrong way i do not need to use the brand from allocated budget i need to use the brand from the product table and now it works so you see it shows at 214 which is the sales in 2009. now i need to divide this number by this number and multiply by this number that will provide the correct value for the projected budget by the way it's useful that we found that problem because it also shows you that columns that should not be used need to be hidden we never want to use columns from allocated budget to perform the filtering because otherwise that creates issues that are kind of complex to find so last step is go to the protected budget where are you protected budget and all the work that we have done to compute this condition is actually not useful because now that we know how to allocate budget at the correct level we can simply divide let's use a variable percentage that is the division of sales in 2009 by sales in 2009 at budget grain um but jet value and then we return budget value times the percentage actually instead of returning budget value by percentage it's useful to return the percentage to see the value we format it as a percentage that gives you a better picture you see that the value we are computing now is just a percentage and it shows the percentage of the total that we need to allocate row by row once we are confident that the number is what we want we just multiply that by the budget value and we format again as a decimal number now we have the total that is still correct and we are allocated by color the different values we can get rid of useless measures like this budget grain i have my new protected budget that allocates by caller and it allocates by whatever so from the product i can get rid of the color i can add the category and the allocation is dynamic and it just works in the case of china a datum we have only one category and the budget shows the complete value for adventure works we have three categories which are allocated using the sales in the previous year now before we move any further one important detail is that this measure is still wrong so do not run and use it in your models because it is still incorrect whenever you work at a different granularity so you have tables that have a link that is not at the key level you need to pay a lot of attention to the details because calculations can be completed can be executed but you always need to go at the correct granularity you can not pre-aggregate values and then compute further numbers let me explain you that with some numbers or with the aid of some visual let's go back to our sales distribution value the numbers we have used to do the allocation are coming from these cells now this cell for example adventure works in china is telling me that adventure works in china in january should have zero as its allocation factor and if i do the calculation at this grain i can compute that number but if i remove the brand and i only sorry if i remove the country let's forget about china and i use the aggregated values of all the countries together you see that adventure works in china has no sales in germany it has no sales but in united states adventure works has an amount of sales if i only take the aggregated value i will take 259 for ada for adventure works across all the countries and then i will allocate it based on allocate some values also for china that would be wrong if you want to do any kind of calculation at the correct granularity i need to perform the calculation at the grain that is supported so my measure my protected budget where are you protected budget cannot be computed that way i need to make it a bit more complex what i need to do is force the calculation let's do that with another measure let me go back here we have the projected budget and let's create a new measure which we call the correct budget correct budgets need to iterate over values of product brand cross-joined with values of customer country region and then compute this entire calculation for only the combination that we are currently iterating on and i bet that we also need to keep filters around it just in case there are some arbitrary shapes set that in this case are very very dangerous now the correct budget that does the same calculation that we have seen earlier so it's the same calculation but it guarantees that the calculation happens at the granularity of brand and the counter region which is the supported granularity of my budget let's format it as a decimal number we put it here and numbers are now the same if we slice up the correct granularity but if i remove the china for example let me get rid of counter region you see that now the values of the correct budget are slightly different than the value of the protected budget protected budget is potentially wrong correct budget is the correct one that's why the best thing that we can do at this point is get rid of protected budget that is showing a wrong number and just look at the correct budget let's look again at the code of correct budget you see that it's iterating over the cross join of brand and counter region to guarantee that the calculation happens at the correct granularity and finally it does the calculation using the percentage of the sales in 2009 against the previous year now that was quite a long trip in order to write a complex expression but the main goal was to show you how you can leverage dax to compute values which are totally not trivial and when it comes to budgeting this is typically the first step because then you can do further calculations on top of this on top of this basic budgeting measure what i want to show you is the final result the final result applies further calculations on top of these base format that was actually the really complex piece of code to compute for example the budget year to date or to show actual same forecast at the same time in the same report so let me okay find this solution now this is the same data model i only added further calculation so it has the same sale allocation i call this time the measure forecast instead of calling protected budget but it's the same calculation and if you look at the forecast calculation it's interesting because what i did was going one step further i mean you remember that we spent quite a lot of time creating this calculated table the allocated budget now if you remember the code of allocated budget it was that is gigantic query that we created in order to multiply by 12 the number of rows in the budget table the thing is now that we learned that we can dynamically allocate values what's the point of allocating them statically we can also allocate by data using the very same technique that we used for the brand and for the counter region so there was no need to create the table even though it was useful in the learning process because it was useful as a warm up to understand all the details but in the real world solution you can get rid of that calculated table and just work with the straight tables and that's exactly what i did in the final model the final model contains the budget and it only contains limited relationships with customer with data the relationship is based on a year it only contains 2010 so it is a relationship again at a different granularity and the third relationship with product again at the different granularity the code of the measure is slightly different but it's just a variation of the original code it does a cross join by brand then count region takes the current brand the current country computes the sales in the same period last year because instead of hard code in 2009 i just take the previous year the sales in the previous year at the budget granularity by removing filter from product and restoring the one on the brand removing filter from the customer and restoring the counter region and restoring the full previous year then it divides the sales by the sales of the budget grain this selected scenario is only to provide the default value for the scenario in case the user does not choose one computes the budget value and the result is the percentage times the budget value everything inside a sum x that iterates over the table and now you can compute easily the forecast that slices by year by month you can choose a one individual category and all these numbers are correct what this report does is going one step further i'm not going to explain you the formulas in this specific report because mainly these formulas are useful to show actuals and forecasts in the same chart and we already have a video that explains that part of the code but that video does not explain this formula so by mixing them together you will have the full picture of how to compute an allocated budget and also how to show that in the same scenario so the beautif the beauty of this forecast measure is that you can use it to compute the ytd of the forecast how do i compute the weighted of the forecast well because forecast has been created the correct way just by touching the correct filter and not messing up with other values if you want to compute the year-to-date of a forecast you only need to compute calculate the forecaster data of data and that just works because the filter context is handled correctly by all the different parts of the code so i can compute the year-to-date of the forecast i can compute the remaining forecast that forecasted remains and all this goes to the final result that i wanted to show you that you can create a chart like this that shows the actual values the actuals as long as you have actuals and then the line of the actuals is extended with the forecast so you can show the actuals up to some point and the forecast for the future still maintaining the full maintaining the system that just works with any slicer so if i filter by any column here i'm using the category but i can choose the category well black maybe is not the best choice black is typically better i'm filtering by category by caller and i'm slicing both sales and forecasts and i'm also extending the value of sales with the values of her of forecast now time to draw some conclusion from the entire session as you have seen i have not shown you how to create a budgeting solution the idea of the entire session is basically food for thoughts i wanted to show you how you can leverage dax to compute values that are definitely not trivial we started with a simple excel file containing a few numbers but by manipulating them by massaging the data by changing computing percentage we have been able to compute to create a solution that means lies budget and sales with any column in the data model it is true it is creating numbers out of nowhere so we do not have a budget for red contoso products in china but we can create the number out of thin air it's not just we're creating it we are reasonably creating it we are using an allocation factor that was decided as the ratio of sales in the previous year against the total sales in your specific business you might have the need to use different ratios different calculations but the overall logic is still the same that's why i think it's important to learn the details of dax because that gives you a tremendous power and lets you create in a matter of a few hours a budgeting solution that might not be perfect but it's a good starting point to gather better insights from your data enjoy dax

Channel: SQLBI

Views: 41,092

Keywords: DAX, PowerBI, Power BI, Alberto Ferrari, SESSION

Id: DteXpeZNudY

Length: 69min 12sec (4152 seconds)

Published: Fri Feb 05 2021