Microsoft Excel Power Query: Calculate Total Amount for Each Customer
Question details
The user needs to calculate the total amount associated with each customer from source records.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Customer transaction data contains multiple records per customer, and the user wants a grouped total for reporting.
- Observed behavior
- The desired result is one summarized row per customer with the Amount values added together.
Confirm that the source table has a consistent Customer column and a numeric Amount column. Load the data into Power Query and check that Amount is assigned a numeric data type before grouping.
Group records by Customer and sum the Amount column in Power Query
Use this method when each customer appears in multiple source rows and you need one aggregated total per customer.
Power Query's Group By command combines rows with the same customer value and applies an aggregation such as Sum to the Amount field. You can add calculated columns after the grouping step if the report needs additional metrics.
In Excel, select a cell in the source table, open the Data tab, and choose From Table/Range to launch Power Query Editor.
In Power Query Editor, select the Customer column, open the Home tab, and click Group By.
In the Group By dialog box, set Group by to Customer, choose Sum under Operation, select Amount as the column to aggregate, and enter a name such as Total Amount.
Click OK to create the grouped results, review the customer totals, and choose Home > Close & Load to return the result to Excel.
If needed, open Add Column > Custom Column and create a Share expression using each customer's Total Amount divided by the overall total.

Use Power Pivot for customer totals in a data model
Choose this alternative when the totals must remain dynamic across relationships, filters, or multiple tables.
Use WPS Spreadsheets to summarize customer amounts in a familiar worksheet
WPS Office is a free, lightweight Microsoft Office alternative for organizing customer records, calculating totals, and creating reports. Although WPS does not run Microsoft Power Query, you can open compatible files, use spreadsheet formulas and PivotTables, and migrate workflows with a familiar interface.
- 1. Open the source data: Launch WPS Spreadsheets, choose Open, and select the XLSX or CSV file containing the Customer and Amount columns.
- 2. Create a customer summary: Use Insert > PivotTable, place Customer in the Rows area, and place Amount in the Values area with the value setting configured as Sum.
- 3. Save in an Office-compatible format: Choose Menu > Save As and select Excel Workbook (*.xlsx) when the summarized report needs to be shared with Microsoft Excel users.

Frequently Asked Questions
Why can I not select Sum in the Power Query Group By dialog?
The Amount column is usually stored as text. In Power Query Editor, select Amount, choose Transform > Data Type, and change it to Whole Number, Decimal Number, or another numeric type before opening Group By.
How do I group customer totals when customer names have inconsistent spelling?
Clean the Customer column before grouping. Use Transform > Format > Trim to remove extra spaces and apply Replace Values or a standardization step to correct spelling differences.
Can Power Query calculate a percentage share for each customer?
Yes. After creating Total Amount with Group By, use Add Column > Custom Column to divide each customer's total by the overall total. Make sure the denominator is numeric and format the result as a percentage.
Should I use Power Query or Power Pivot for customer totals?
Use Power Query for repeatable data cleaning and summarization during the import process. Use Power Pivot when totals must remain dynamic in a data model and respond to relationships, filters, or PivotTable selections.




