How to Create Power Pivot DAX Measures for Conditional Amounts and Percentages
Question details
The user needs Power Pivot DAX measures that return values conditionally and express each row as a percentage of a category total.
- Product
- Microsoft Excel with Power Pivot add-in
- Device & OS
- not provided
- Scenario
- The user is building a Power Pivot model and wants to create conditional amount measures plus percentage-of-category-total measures for use in a PivotTable.
- Observed behavior
- The desired state is to have accurate conditional amount values and percentage totals that respond to the PivotTable filter context.
Open the workbook in Microsoft Excel, enable the Power Pivot add-in, and confirm that the source table is loaded into the Data Model with the expected column names. Replace Table1 and the sample column names with your real table and column names.
Create a Conditional Amount Measure with SUMX and IF
Use this measure when an amount should be calculated only for rows that meet a condition, such as having a non-blank Actuals Allocated value.
This pattern multiplies the driver amount by 1 when the condition is true and by BLANK() when it is not, so non-qualifying rows do not contribute to the total.
In Excel, open the Power Pivot window, select the table in the calculation area, and click New Measure on the Power Pivot or Data Model tab.
In the Measure dialog, enter the name Amount, $ and the formula: SUMX(Table1, Table1[Driver Amount] * IF(ISBLANK(Table1[Actuals Allocated]), BLANK(), 1)). Set the number format to currency and click OK.
Insert a PivotTable from the Data Model, add Category to Rows and the Amount, $ measure to Values, and confirm that only rows with Actuals Allocated show amounts.
Calculate Each Row as a Percentage of the Category Total with CALCULATE and ALL
Use this measure to show a value as a percentage of its category total while ignoring the current row filter.
Use WPS Spreadsheets for Pivot Table Summaries and Percentage of Category Totals
Power Pivot DAX is a Microsoft Excel feature. WPS Office does not include Power Pivot, but WPS Spreadsheets is a free, lightweight Office alternative that opens .xlsx files and handles pivot table summaries, percentage calculations, and everyday spreadsheet work with a familiar ribbon interface.
- 1. Download WPS Office: Download WPS Office from the official WPS site and run the installer for your Windows or Mac device.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open the .xlsx file that contains your source data or PivotTable requirements.
- 3. Insert a PivotTable: Select the data range, go to the Insert tab, click PivotTable, and choose to place it in a new worksheet.
- 4. Show values as percentage: Add Category to Rows and the amount field to Values, then use Value Field Settings > Show Values As and select % of Column Total or % of Parent Row Total for category percentage views.
Frequently Asked Questions
Does WPS Office support Power Pivot or DAX measures?
No. Power Pivot and DAX are Microsoft Excel Data Model features. WPS Spreadsheets can open .xlsx files and create pivot tables, but it does not provide a Power Pivot window or DAX measure editor.
Can I use these DAX formulas in WPS Spreadsheets?
No, DAX formulas are not supported in WPS Spreadsheets. Use WPS pivot table percentage options, SUMIF/SUMIFS helper columns, or keep the DAX workbook in Microsoft Excel.
How do I calculate a percentage of category total in WPS Spreadsheets without DAX?
Select your PivotTable, open Value Field Settings, choose Show Values As, and select % of Column Total, % of Parent Total, or a similar option. Alternatively add a helper column with SUMIF and divide.
Which DAX functions are used for conditional amounts and percentage of total?
The typical pattern uses SUM or SUMX to aggregate, IF or ISBLANK to apply conditions, CALCULATE with ALL to remove a category filter, and DIVIDE to return a safe percentage.
