This blog will explain how we can use the context filter function in Power BI. The DAX statements used in this blog are ALL, ALLEXCEPT and ALLSELECTED.
The dataset applied in this blog is Sample Superstore which you can be easily found in the tableau repository.
After loading the data, you can drag both the region and sales in the canvas, so that you can get the following table.
The first requirement that we want to achieve is to calculate the percentage of sales from each region.
To calculate the percentage, we need to get the total sales and populate it as a new column. Therefore, we need to use the ALL statement here.
The function of the ALL statement with the table name as a parameter is to remove all the context filters in the query. For those who don’t have much Power BI background, the calculate function allows us to change the context filter of our expressions.
Then, we can use the individual sales from different regions to divide the total sales to get the percentage.
It looks all good now. But if you put the segment into the table, you will find the result of the percentage is not correct. Namely, for the same region, the percentage is not the same. This is because the table is filtered by the segment now. To correct this, we need to remove the context filter of the segment with the following expression.
Now, you can see the percentages from the same region are the same.
But one potential defect of this expression is that when we drag another dimension into the table-like category, the query will be filtered by the new dimension and the result will get wrong again.
To fix this potential issue, I will demonstrate how we can use AllEXCEPT statement to improve the formula in the next blog.