Power BI DAX Tutorial (14/50) - What is ALL and ALLExcept

welcome to analytics with Nags in this video  we are going to see about what is all and all   except functions index we're exploring power  we are using a business scenario naga garments   and for this business how these two functions  will be helpful let us see it in a demo   if you are new to this channel hit the subscribe  button right now to learn the concepts in power bi we have a simple model here having sales and  two tables that those who are following my   channel they are familiar with and now let  us try to understand all so all function is   nothing but it will return a table and mean  it will remove all the filters from the table   so it can be used in many purpose one of the  purpose of all function is to create a new table   let's say i want a duplicate table  of product master so what i can do is   let's say all of i can put  product master okay then product reference something like that okay  maybe i need the distinct columns of   all the values something like that i can go for  it so for now i'll say all of product master i   kept it as product reference so i get immediately  a new table with all the column names okay you can   go and see here product reference so it is same  and you see product my original table it is same   even in the relationship tab you will have a new  table here okay there might be some scenarios   you need a reference table okay you need a  copy of some table in that case you can go for   all function that is point number one so next  i want to highlight another important use of   all tax function is like now i'm seeing the  total sales by wise category wise total sales   how much this each category is contributing to the  total sales that i want to understand so for that   what i can do is i can create a new  measure okay so what i need to do is all sales let me take calculate total sales that is my default  measure total sales is very simple   let me zoom in little bit of  this calculation comma all of   sales table okay first i am putting the entire  table now calculate comma all what happened i'm i closed it so i thought end  of the function so i will put sales   let us put sales now then let  us check what are other options   okay now this is all sales now when i put  this measure in my table you look at here   this all sales returns the total of your sales  so when i put this all cells as a label here visualization data labels instead of data label  i will put none then i will say the category   the size i will increase so that you can see  what is this okay so this is your all sales   so all sales is nothing but the all function is  removes all the filters applied in your table   so when you say all of sales so it consider enter  table you filter by state as you filter by order   type it ignore all the filters so the overall sum  of a particular column whatever you aggregated   okay that will be shown so now when i apply  chennai you see there is no change when i apply   accessories there is no change it indicates like  the entire total cell this whole figure will be   displayed that is what this all function will  do so all means all the records in your table   ignore any filter applies on that table that is  your all so on what scenarios you'll use so now   you want to have this percentage how much it has  contributed you usually do this one okay so this   is one thing so let us try to create a new measure  to create a percentage how much it is contributed percentage of category sales okay so here i'll say like um calculate or  i can just go by divide my total sales comma all sales okay so my percentage category  sales instead of this i need to put it here   then make it as percentage okay now you see here  my 63 percentage of sales is from cash flow rear   and my accessories is very less okay so in  this way he can able to understand why his   sales is happening so if you see in  percentage it will be very easy to   identify so that is where your all function  plays a vital role here let's see our next   function that is all except okay now let us  try to create all except function in what   scenarios we'll use we'll check it later i  mean after creating a measure so new measure sales category all   accept okay yeah usually it will not be like  this you have a proper naming convention   i mean based on your measure values you can put  that one all accept so what is the syntax is like   you need to specify all except a table name so i  want sales table comma i want a product category okay i am not using the column  reference from the dimension   i am having everything in a single table okay okay now we have this all acceptor measure  created so what it will do is it will   ignore all the filters applied on the sales  table and accept this product category   okay what i meant let us see it quickly in data  now let me put this one here okay this shows   both uh same data since it will keep the filters  of this product category and ignore all other   filters okay let us try to express this one by  clicking on a location so what all accept is doing you see here it ignores this location filter  right that is what this all accept is doing it   removes all the filters in the sales  and accept this product category   okay that is the main difference between all and  all except so let us try to understand it better   by putting a product name also so when we  put product name even here this ignores this   all except ignores this filter you understand this  filter context now like this works as the total   sales is the base uh measure that works with the  proper filter context that is the formula is sum   of sales so this total sale splits by each filter  context and displaying the total sales for this   bangalore but what happened to all except when  i uncheck it the total of this accessories right   where we can see it here like this is my total  of accessories so this total is appearing for   both the accessories here right even when i click  on chennai that doesn't matter even for chennai   and this chain wallet it doesn't filter okay so  ignore all the filters from the other columns in   the table keep only the filters for this category  that is what the all accept is doing all about so   in what scenarios you will use obviously it's like  um how much contribution this has made against   each product say for example here if the user  chooses here for this particular location in this   location his total sales of accessories is 195  whereas across all accessories different locations   this is his total so he wants to analyze this  percentage then you can use the divide and use   that one so like this people want to analyze the  percentage irrespective of the filters they apply   i hope you guys understand when to use all  and all except if you like this video share   and subscribe to the channel comment below for  your queries do remember that data is your asset

Channel: Analytics with Nags

Views: 28,117

Keywords: power bi dashboard tutorial, power bi dashboard, power bi demo, power bi desktop for beginners, power bi desktop tutorial, power bi desktop, power bi for beginners, power bi training, power bi tutorial for beginners, power bi tutorial, Power bi, powerbi, power bi full course, power bi dax, power bi dashboard examples, power bi course, Microsoft Power BI, simplilearn power bi, intellipaat power bi, Analytics with Nags, Avi Singh, Guy in a Cube, Curbal, Alberto Ferrari

Id: TyDCNZdrTJc

Length: 11min 27sec (687 seconds)

Published: Sat Aug 08 2020