The FILTER function as we know will return a filtered table, and the ALL function will force ignore all outside filter context and apply the expression on ALL items where Sales is greater than 100. Let’s call the measure Sales Quantity > 100 equiv (for equivalent of the previous measure). So we’re gonna FILTER ALL values from the Sales table where the Sales is greater than 100. Let’s do a CALCULATE, where we SUM the Sales but in this case let’s add a FILTER function and the ALL function. Let’s do another few measures just to explain how calculate works.Ī quick note on adding new measures: Do you know that the table that you last selected or clicked on is where a new measure gets added when you click on the ‘New Measure’ button? If you want to easily place that measure in a specific group, you can just right click on the table or place holder and then select, ‘New Measure’.īack to our measure, let’s do another measure where the sales quantity is greater than 100, but this time I want to add a few other interesting functions into our calculation. If you want to review that please go and have a look at the last week’s video where we looked at SUM, SUMX and CALCULATE. That’s the key difference between SUM and SUMX. So for each row where it finds a quantity larger than 100, it will calculate the Sales * Sales and store that value, and once it has reached the bottom of our table, it will SUM all those values together and return the answer. SUMX will go row by row and will start with the Sales filter.
The difference with SUMX and SUM is in the way it evaluates. Okay great so it seems like it does the same thing, right?! We can see that the answer from our SUM and from the SUMX is the same. Format to British Pound and let’s put it on the canvas. Start with CALCULATE and use a SUMX of the ‘Sales’ table and multiply the Sales by the Sales (the Quantity) and then finally let’s include a filter where the Sales > 100. But this time I’m going to use SUMX instead of SUM, so let’s see how that works. I want to do a similar calculation than before where I’m going to calculate the total sales where the quantity again is greater than 100. It then sums up the total sales together to give us an answer for each of the CUSTOMERNAMEs. Then it goes to the inner filter context (inside the DAX CALCULATE function) where it takes the quantity column and applies a filter where the sales quantity is greater than 100. If we look at the Sales table from the Data view pane, we can see what it has done is to filter on the CUSTOMERNAME for each row in the table (ACME Plumbing for example), which is the outer filter context. The reason for that is that our new ‘Sales Quantity > 100’ measure has filtered the sales quantity on the inside of the evaluation, but it’s done so after it’s applied the outer filter context of each CUSTOMERNAMEs. We can see that the values of the ‘Sales Quantity > 100’ measure is less than the original total ‘Sales’ measure values. I want to do a CALCULATE of the sales measure we created earlier called ‘Sales’ where the sales quantity is greater than 100. For ease of reference, I’m going to call it ‘Sales quantity > than 100’. I want to do the sales amount where the sales quantity is greater than 100. Using the matrix example above, we know that irrespective of the filter context from the measure itself, the actual CUSTOMERNAMEs will also apply an outer filter context by showing us sales values filtered by each CUSTOMERNAME. If we look at how CALCULATE filters are evaluated, it first looks at the outer filter context before applying the inner filter context (inside the CALCULATE – referred to as inner filter context) and then continues to calculate the DAX expression. Let’s have a look at how CALCULATE works. Add CUSTOMERNAME as the row value and ‘Sales’ as the values. Let’s use a matrix visual and show the ‘Sales’ by CUSTOMERNAME. Let’s format the measure to British pound. For this measure we are going to do the sum of the sales table from the total sales column.
To start off let’s do our first calculation which will act as the base calculation for our Sales measures. Let’s jump in deeper to see how this works.
0 kommentar(er)
