'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Create a DAX Percentage Measure That Respects Slicers in Excel
Question details
The user needs to create a DAX measure for percentage calculations that dynamically totals 100% based on active slicers and filters, rather than calculating against the entire dataset.
- Product
- Microsoft Excel / Power BI
- Device & OS
- not provided
- Scenario
- Calculating accurate categorical percentages (such as ethnicity) based on dynamically filtered data via slicers.
- Observed behavior
- The percentage calculation needs to evaluate values only against the currently filtered context instead of unconditionally evaluating against all categories.
Ensure that your data model is correctly loaded into your workbook and that your slicers are properly connected to the relevant data tables.
Use the ALLSELECTED Function for Dynamic Denominators
Apply the ALLSELECTED function within a CALCULATE statement to ensure your percentage denominator dynamically respects active slicers.
The ALLSELECTED function removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters. This makes it the ideal DAX function for calculating percentages that need to total 100% based only on the filtered items.
Navigate to the Power Pivot tab in Excel or the Modeling tab in Power BI, and select 'New Measure'.
Input the following formula: Rating % = DIVIDE([Talent Rating ALL], CALCULATE([Talent Rating ALL], ALLSELECTED('Talent Rating'[Ethnicity grouped]))).
Save the measure. Instead of multiplying the formula by 100, select the newly created measure and click the '%' icon in the formatting ribbon to display it correctly as a percentage.
Looking for a Lightweight and Free Excel Alternative?
While advanced DAX data modeling and Power Pivot are specific to Microsoft environments, WPS Office provides a powerful, free, and lightweight alternative for everyday spreadsheet data analysis. Enjoy seamless compatibility with Microsoft Excel formats alongside intuitive PivotTables, charts, and built-in formulas for your daily reporting needs.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open your dataset: Launch WPS Spreadsheet and open your existing Excel workbook.
- 3. Analyze with PivotTables: Go to the Insert tab and select PivotTable to analyze and filter your data dynamically without requiring advanced data modeling.
Frequently Asked Questions
Why is my DAX measure not changing when I click a slicer?
You might be using the ALL function instead of ALLSELECTED in your CALCULATE statement. The ALL function removes all filters entirely, ignoring your slicer selections, whereas ALLSELECTED respects filters applied outside the visual.
Should I manually multiply my DAX percentage formula by 100?
No, it is highly recommended to output the raw decimal value from the DIVIDE function and format the measure itself as a percentage using the data formatting tab. This ensures accurate aggregation and charting.
Can I use this ALLSELECTED DAX formula in Excel Power Pivot?
Yes, the ALLSELECTED DAX function behaves identically in both Microsoft Excel Power Pivot and Power BI for managing filter contexts and slicer interactions.
