Calculate Each Month's Percentage of the Total in Excel Power Query
Question details
Calculate each month's share of the total Amount column in Power Query.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A table contains Month and Amount columns, and the user wants to calculate each month's contribution to the grand total.
- Observed behavior
- The desired result is a percentage column showing each monthly amount divided by the sum of all amounts.
Check that your table has Month and Amount column headers, that Amount values are numeric, and that their grand total is not zero. If a month appears on multiple rows, group the rows by Month and sum Amount first if you need one percentage per month.
Calculate monthly contributions with Power Query
Use Power Query to calculate the grand total and divide each monthly amount by it.
Select the Month and Amount data, press Ctrl+T, and confirm that the table has headers. On the Table Design tab, check the table name; the example below uses Table1.
Select a cell in the table, then choose Data > From Table/Range.
In Power Query Editor, choose View > Advanced Editor. Replace the query with the following code, changing Table1 if your table has a different name: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ChangedType = Table.TransformColumnTypes(Source, {{"Month", type text}, {"Amount", type number}}), AddedTotal = Table.AddColumn(ChangedType, "Total", each List.Sum(ChangedType[Amount]), type number), AddedContribution = Table.AddColumn(AddedTotal, "Contribution (%)", each [Amount] / [Total], Percentage.Type), RemovedTotal = Table.RemoveColumns(AddedContribution, {"Total"}) in RemovedTotal Select Done.
Choose Home > Close & Load. In the loaded Excel table, select the Contribution (%) column and choose Home > Percent Style if the values are not already displayed as percentages.

Calculate monthly contributions in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel files. It does not fix Excel Power Query, but you can calculate the same contribution percentages with a formula and continue working with your workbook.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and locate the Month and Amount columns.
- 2. Enter the contribution formula: If amounts are in B2:B13, enter =B2/SUM($B$2:$B$13) in C2. Adjust the range to include all your amount rows.
- 3. Fill and format the results: Drag the fill handle from C2 down through the remaining rows, then select the results and choose Home > Percent Style.

Frequently Asked Questions
How do I update the percentages after changing the source data?
Update the source Excel table, then select Data > Refresh All. Power Query recalculates the total and each row's contribution.
Why does the query show a division error?
Check that the Amount column contains numeric values and that its grand total is not zero. Division by zero causes an error.
What if the same month appears on several rows?
The example calculates a contribution for each row. For one result per month, use Home > Group By in Power Query to group by Month and sum Amount before calculating percentages.
Why does Excel show decimals instead of percentages?
A value such as 0.25 represents 25%. Select the loaded contribution column and choose Home > Percent Style to display it as a percentage.




