Use ALLSELECTED() in Power BI to Fix Incorrect Total Calculations
Table of Contents
Introduction
When working with Microsoft Power BI, it is crucial to understand how filters affect your calculations, like ALLSELECTED(). The ALL() and REMOVEFILTERS() functions are some of the most used functions for handling filter contexts. However, these functions do not always work as expected, especially when working with slicers.
If you’re new to how ALL() works, you can first explore this guide: Using ALL() to Ignore Filters, which explains how filters can be removed in calculations. The ALL() function typically ignores filters and returns all data, acting like a reset for filter context.
In this blog, you will work with a real-world problem where these two functions do not work as expected. Additionally, you will learn how to use the ALLSELECTED() function to create better and more dynamic measures.
Using ALLSELECTED()
In this section, you will learn when functions like ALL() and how to create robust measures.
To understand this concept:
- Download and open the Power BI file (ALLSELECTED.pbix) from the link provided below the blog and follow the step-by-step instructions to practice.
- Click on Slicer in the Visualization Pane.
- Drag and drop Group from SalesTerritoty to the Field.
- Now, click on Europe.
- Click on Matrix in the Visualization pane.
- Drag and drop Country from the Sales Territory table into the Rows field.
- Drag and drop Total Sales Amount (CM) from the All Measures table into the Values field.
- Resize the matrix visual if required for better visibility.
Observe that the percentage numbers are being influenced by the selection of the slicer. Instead of computing the percentage with respect to the total sales figure, the percentage will now be computed with regard to the selected region alone (which is Europe, in this case). In this manner, the total shows a value of 18.03%, rather than 100%.
In order to compute percentages with respect to selected data while maintaining the correct totals, the use of ALLSELECTED() can prove helpful.
To see how ALLSELECTED() helps solve this problem:
9. Right click on All Measures in the Fields.
10. Click on New measure.
Measure tools open.
11. Type the following formula in the formula bar and press Enter.
You do not need to format this measure because it is an intermediate measure.
Let’s use this measure to find the percentage of sales.
12. Right click on All Measures in the Fields.
13. Click on New measure.
Measure tools open.
14.Type the following formula in the formula bar and press Enter.
15. Click on the “%” in the formatting section.
16. Drag and drop ALL Selected Country Sales (CM) and % of Total Sales using ALLSELECTED (CM) from the All Measures table to the Values.
17. Now, click on Europe.
Conclusion
From this blog, you have learned how functions can sometimes not respect what the slicers have chosen, which can lead to incorrect totals. Using ALLSELECTED() inPower BI, however, can help ensure that your measures respect what the user has chosen, while still maintaining the correct percentages.
This will help you design more interactive and accurate reports in Power BI, especially when working with slicers. It is essential to master these concepts to help you design more robust and user-friendly reports.
FAQs
ALLSELECTED() is a DAX function that returns all rows in a table while keeping filters applied by slicers and user selections.
ALL() removes all filters completely, whereas ALLSELECTED() retains filters applied through slicers and visuals, making it more suitable for interactive reports.
Because ALL() ignores slicer filters, it calculates totals based on the entire dataset instead of the selected subset.
Extended Learning & References
For more in-depth learning and advanced Power BI training, enrol in
- Microsoft 365 Copilot Training Courses – Practical Learning for Modern Work
- Power BI Advanced Training Course.
- Power BI Report Design
- Power BI Essentials
- Power BI DAX Essentials
- Power BI Service Course
Here, you can access detailed courses and resources designed to enhance your Power BI skills and take your reporting capabilities to the next level.
Downloads
