How to Sum Row Minimums in an Excel Power Pivot Total Row
Question details
The user needs a Power Pivot measure that shows the minimum value for each row in a pivot table and sums those row-level minimums in the total row.
- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- The user is building a monthly Power Pivot report where each month row should show its minimum value, and the grand total should be the sum of the monthly minimums.
- Observed behavior
- A standard MIN or SUM measure does not switch between row-level and total-level calculation, so the total row does not display the intended sum.
Before creating the measure, enable the Power Pivot add-in in Excel and confirm that your data model includes a date table with a Month column, because the measure relies on HASONEVALUE over that column.
Create a DAX measure that returns MIN for one month and sums row minimums in totals
Use this when you need a single measure that changes context: row-level MIN for each month and total-level SUM of those minimums.
Power Pivot calculates measures in the current filter context. HASONEVALUE checks whether the Date[Month] column has a single selected month; SUMX iterates over each month in the total context and sums the monthly MIN results.
In Excel, go to the Power Pivot tab and click Manage Data Model to open the model window.
In the data model, select the table that contains the values you want to minimize.
In the calculation area below the table, enter: =IF(HASONEVALUE('Date'[Month]), MIN('Table'[Value]), SUMX(VALUES('Date'[Month]), MIN('Table'[Value]))), replacing 'Date', 'Table', and 'Value' with your actual names.
Return to the workbook, click the PivotTable, and drag the new measure into the Values area.
Filter to a single month to confirm the row shows MIN; clear the month filter and check that the total row sums the displayed monthly minimums.
Use WPS Office Spreadsheets as a Free Alternative for Standard Pivot Tables
WPS Office Spreadsheets is a free, lightweight Excel alternative that opens .xlsx workbooks, preserves standard PivotTables, and offers a familiar ribbon interface. It is ideal when you do not require the full Power Pivot data model and want easier everyday pivot table reporting.
- 1. Download WPS Office: Go to the official WPS website and click Download WPS Office Free to install WPS Office on your computer.
- 2. Open the Excel workbook: Launch WPS Spreadsheets and open the .xlsx workbook you were using in Excel.
- 3. Create or refresh a standard PivotTable: Select your data range, click Insert > PivotTable, and place the pivot table on a new sheet.
- 4. Summarize row and total values: Drag the relevant field to Rows and the value field to Values, then set value calculation to Min or Sum as needed.
Frequently Asked Questions
Why does my Power Pivot total row show MIN instead of summing the row minimums?
A single measure returns one calculation for the current context. Create an explicit measure that tests HASONEVALUE(Date[Month]); when false, wrap VALUES(Date[Month]) in SUMX so each month's MIN is summed.
Can WPS Office create Power Pivot data models?
No, Power Pivot is a Microsoft Excel add-in. WPS Office Spreadsheets can open Excel workbooks and build standard PivotTables, but it does not include the Power Pivot DAX data model.
Where do I enter the HASONEVALUE measure in Excel?
Open the Power Pivot tab, click Manage Data Model, select your table, and type the measure in the calculation area below the table.
Do I need a separate date table for this measure?
Yes. A date table with one row per date and a Month column lets HASONEVALUE return the right single-month context.
What if my data has blank months or multiple value columns?
Filter blanks in the source or date table, and create the measure for the specific value column that contains the numbers you want to minimize.
