Dealing with Incorrect Totals in Power BI

one is two plus two plus one the answer four that's not me that is power bi power bi at times can give you awkward totals that it believes to be the right way to calculate totals but it is not right if a business user takes a look at those totals it's an absolute disaster probably even a fireable offense in this video I'm going to talk about the reasons for the incorrect totals and how do you fix them all right no further ado let's go check it out all right in the first part we talk about the reasons for incorrect totals and before we do that let's just take a look at the data model that we are working with I have a very simple Dimension product stable and a dimension calendar table and this is linked using the product code or the product ID with my products table and the date is linked with the date in the calendar table nothing that complicated very straightforward and I've created two simple visuals out here using these visuals I'm going to talk about incorrect total and a few examples take a look here I have dragged the channel from the sales table and against that I have created a unique days calculation that is simply doing a distinct count of the date of the sale in the sales table and I have dragged that particular calculation right here now if you literally take a look at this you're going to see that affiliate products sold 290 days which is unique days 101 days for organic and 305 days for promotion if you add these numbers up I'm probably looking at a number around 600 and that is not what I see I get to see 464. why is that I'm sure you can make out the reason of totals not matching which is very very simple there were possibly a few days in the affiliate that were also matching in the organic Channel taken individually these days are unique but if you take a look at the total these days are still duplicated which would have been removed from the total and therefore you see a much smaller total take a look at another such type of calculation I have the product from the product stable dragged right here and against that I have the maximum transaction sales value and I can see that these are all the max transaction values for the product but if I try to add them up I'm not going to get 218 I'm gonna get a value Way Beyond that but here 218 represents the maximum transaction value in the entire data so why do these totals do not match in here if you take a look at the two examples that we are working with we're working with something called as a non-additive measure that means if you were to take individual rows it is likely that the totals are not going to add up to the individual rows because at the total level the filter context is different let me help you understand probably let's just take an example of this particular product when the filter context is applied the Dax calculation is perhaps taking a look at only balance sheet template data and hence you get the value as 75 but if no filters are applied it doesn't really take a look at individual products and add them up it takes a look at the entire data and gets to the answer of 218. we can even check that if we physically take a look at that data so here is a copy of the data that I have loaded in power bi brought into Excel if I were to apply a filter on balance sheet template it and if I were to sort this data from ascending to descending order I'm going to see that the maximum value is 75 which is matching this is the maximum transaction value and if I were to remove the total and sort this data in the descending order the max value that I'm going to get is about 218 and that is the answer that we were taking a look at in power bi as well all right now that you've understood that why the totals do not really match the individual rows what can we do about it and how can we fix such kind of problems now the thing is that at the total level the filter context is removed and power bi is taking a look at all the data or let's say some other kind of data which by the way doesn't see these individual rows so what you need to do is you have to force Power bi to take a look at individual rows that it has just passed above and total these rows up forcibly at the total level how do we do that let's take a look for me to be able to correct the totals I'll have to revise my calculation just a bit so for that let me just kind of open up DAC Studio help you construct the logic of the problem and then we will take a look at how do we fix the totals I'm going to open up the DAC studio right here I'm in DAC studio and that is where I do not have any filter context just as the way that I have it in power bi so whatever query that I write here is probably going to see the same data as power bi is going to take a look at right here and at the total level I do not really want to see the entire data I want to see affiliate Channel organic Channel and promotional Channel and I want to see these individual numbers that I can sum it up how do we do such a kind of thing so I'm going to use and evaluate to start writing a query in that studio you can only create tables and the keyword that you write to create tables is the evaluate keyboard and I'm going to write a values function which is going to help me remove the duplicates from the sales Channel column so sales table and the column name is Channel and the duplicates are gone and if I just kind of evaluate or run this particular query I'm going to get the same output as I have received in power bi so the channel is affiliate organic and promotional once I have received the channel then I'm going to go ahead and add these three totals as well against that so I can say something like hey here is a table which has just got three rows I would like to add a column to this table and the column can be let's something like a unique days so I'm just going to call this as days and the calculation that I would like to write in here is nothing but a unique days calculation which is nothing but my distinct count measure that I have written close the bracket quickly format my Dax and let's just kind of run this particular query and I get the same output as I have it in power bi so now at the totals level which is where there is no filter context I have the ability to produce the same rows as I have in power bi and the same numbers as well and I can probably do something like this I can say hey please go inside every single row of this particular table and please do the sum of the days that I have just created which is the column that I've just created right here and close the bracket format the Dax now at the moment this is nothing but a measure and you cannot really run a measure in power bi you have to convert the measure into a table so I'm going to create a zero table by initiating a curly braces and close the curly braces in the end and then run my query and the total should be different than 460 D4 let's just take a look now we get about 696 and that seems to me like the right total now how do we implement this particular calculation in power bi because at the moment what we have done is we have created a table and done some sumex work around that how do we implement this kind of thing in power bi so in power bi I have to go ahead and search for my measure and I know that how do I create a table using the values function and in that I will just run my distinct count calculation so I'm going to go something like this I'm going to say hey I want to do a sum X and the table that I'm looking to create is the values table of the sales table Channel column and in each row of the table which is nothing but the organic promotion and affiliate I want to do the distinct count calculation and I also have to kind of wrap this around in the calculate to kind of trigger the context transition and I'm just going to maybe press enter and that gives me the 696 that I was trying to see which is actually the right answer now if you do not understand that why did I include the calculate function right here I have done several videos on context transition I'm going to link all of those videos in the description please do watch and understand what is context transition but this is how I handle the totals problem the same thing can be done to our Max transaction value as well so I can just go in that particular measure right here and I'm going to say hey I'm just trying to sum the results of the unique products right here so I have to create a table which has unique products but guess what the names of the products are unique and that's what we have the products table for we already have a table which has Unique Products so I can just step inside of that particular table and then run my calculation around the calculated function close the bracket for context transition and we're kind of good to go and now if you take a look at the total this food 1145 is the actual total of all the max transactions in the end all right let's just take a look at yet another interesting and probably a tricky example of totals not adding up so here I have the ear I have the category and I have Max sold per day that tells me that what is the maximum sales per day in whatever Dimension that you're trying to take a look at so here I can see that for all the mid segment products for all of them in 2011 in one single day the maximum sales was 288 dollars now obviously if I try to Total them up I will certainly not get 407 dollars and I will get a much higher result how do we fix the totals now the reason why we are getting incorrect totals is because this is again a non-additive measure that means that these individual rows are not going to add up to the total right here and in order for us to fix the total we'll have to make sure that at the total level the filter context is not just 2011 it's not taking a look at the entire 2011 data it is taking a look at 2011 and summarize data for all the categories and the Very numbers right here at the total level and then it will be able to summarize so all of these numbers and then add the numbers up so our attempt is going to be to create this little table right here which is summarized by the year and summarized by the category and feed these numbers up right here so that power bi can do the totals all right let's just go to DAC studio and start to create this table all right I'm in DAC studio and that's where I will start to create a summarize table that looks probably like this to force Power bi to see the same table that it has left in the above rows so that I can make the total of those rows how do we make that I've already started with the evaluate keyword and I'm going to start to write the summarize function so I'll say something like summarize and the table that I want to summarize is the sales table because my sales calculation is coming from there and then I'm gonna say hey the First Column here that I would want to pick up to summarize is by the year which is nothing but the calendar year I'm going to write that so calendar table and the year column right here and the second column that I would like to summarize by is the products table and in that I have the category column and that is my table now let's just start to create a little table right here so I'll click on run and that is the table that I get so I have 2011 of all of these right here let me just zoom in a bit so 2011 and all of these right here that I get to see right here and this is again the data for 2012. now in this table what is missing as of now are the max sold per day calculations and I will just add that using the add columns function and I'm going to say something like add columns and in this table I'm trying to add a column call that column as nothing but let's say Max sales and I am going to use my measure which is nothing but max sold per day right close the bracket and let's just kind of run the Dax right here and that is what I get now if there was no visual impact RBI this is what power bi would see if I were to use something like a summarize function to force Power bi to take a look at the very rows in which the numbers exist now what I can do is I can use this particular summarize and I can feed this off in my power bi max sold per day calculation so I'm just going to copy this right here copy that all right once I have copied the summarize function that creates the table that we just took a look we are going to create a new measure that gives us the right total which is where we'll use the summarize function so I'm going to maybe make a new measure I'm going to call this as Max sold per day correct and in there I'm going to use my summarize function which is nothing but right here and I'm going to say hey why don't you just go through every single row of the summarize which is probably giving you a table like this even at the total level and in this table I want you to calculate my max sold per day which is nothing but this calculation and I want you to sum all the rows of the data that's it check on that now power bi is taking a look at these summarized rows even at the total level and now the totals are going to be right so if I just maybe use the correct measure in the table that I have created let's just take a look at do we get the right totals or not and sure enough we do get the right tools seems right like 600 seems right like 800 and then eventually we are going to have 1400 which is the summation of 800 and 600. that is pretty damn awesome all right that was a quick video about fixing incorrect totals in power bi just to let you know that Greg Deckler has been profusely buying people to vote on the idea that power bi gives us an option to toggle between power bi context totals the one that we saw with semen correct and the actual totals of the row through a toggle button I suggest that you please vote on the idea for the same of course if you have any questions please put them down in the comment and I'll be glad to reply more importantly till the time that problem gets fixed you have to understand that if you want to get the totals right you have to ensure that power bi is taking a look at the summarized data above especially for the non-iritive calculations and then you can sum X through that and make sure that the totals are right these were a few examples I'm sure you can take from these and improve on the wrong totals in your own reports in the end before you go I'd like to give a big shout out about my attacks on the power query training programs in case you are interested and you're a beginner you'd like to start with the fundamentals first and then move on to solving more harder more difficult problems even of your own data I highly recommend that you take a look at my courses it is going to be super awesome thanks so much for sticking all around and I will catch you guys in the next one peace [Music] thank you [Music]

Channel: Goodly

Views: 35,042

Keywords: goodly, chandeep chhabra, Power bi tutorial, power bi dashboard, Measures in power bi, incorrect total in power bi, power bi measure incorrect totals, dax studio in power bi, power bi total error, power bi dax studio external tools, data analyst, data analysis, power bi common errors, Power BI, incorrect totals, fix incorrect totals, non-additive measures, calculate totals accurately, accurate calculations, Power BI context, toggle button, fix totals in Power BI

Id: yw0QHu9V4UQ

Length: 14min 44sec (884 seconds)

Published: Wed Sep 20 2023