Introduction:
Proportion calculations are a fundamental part of data analysis, often involving simple division. However, when the dimensions and scope of your data change dynamically, you need a flexible approach. In this blog post, we’ll explore the power of DAX (Data Analysis Expressions) functions, specifically ALL and ALLSELECTED, in Power BI. We’ll apply these concepts to Superstore data to calculate various proportions effectively.
Sales Measure:
Let’s start by creating a sales measure for our Superstore data:
Total Sales = SUM(‘Orders'[Sales])
This measure calculates the total sales across all sub-categories and categories.
Overall Proportion:
To calculate the proportion of the total sales for a selected category, we can use the ALL function to clear the filter context. Here’s how you can do it:
Overall Proportion = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(‘Orders’)))
In order to further analyze the specific proportion of each sub-category, expand the sub-category names:
This measure computes the proportion of sales for each sub-category concerning the total sales across all categories.
Category Proportion:
If you want to find the proportion of each sub-category within its category, you can use the ALL function on the ‘Sub-Category’ column:
Category Proportion = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(‘Orders'[Sub-Category])))
This measure calculates the proportion of each sub-category’s sales within its respective category.
Calculating Proportions by Filter Context:
Now, let’s say you want to analyze specific sub-categories. You can use a slicer to select these sub-categories. However, if you want to calculate proportions based on the selected sub-categories, the ALLSELECTED function comes into play.
Filter by Total %:
To calculate the proportion of each selected sub-category’s sales concerning the total sales of all selected sub-categories, use this measure:
Filter by Total % = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(‘Orders’)))
This measure ensures that no matter which sub-categories you select, the overall proportion remains 100%, and each sub-category’s proportion relates to the selected sub-categories’.
Filter by Category %:
If you want to calculate the relative sub-category percentage based on the selected sub-categories and categories, you can use the ALLSELECTED function on both the ‘Category’ and ‘Sub-Category’:
Filter by Category % = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(‘Orders'[Category], ‘Orders'[Sub-Category])))
This measure provides the expected result, offering a flexible way to analyze proportions based on various contexts.
Summarize:
In summary, DAX offers a powerful way to calculate proportions flexibly in Power BI. By carefully selecting the ALL and ALLSELECTED functions and their parameters, you can control the calculation’s context and obtain the desired proportions.
DAX functions may seem complex, but with practice and an understanding of context, you can master them to perform sophisticated proportion calculations in your data analysis projects.