Power Query Problems

Calculate Each Month's Percentage of the Total in Excel Power Query

Kushani NimanthikaKushani Nimanthika Nov 20, 2026 869 views

Question details

Calculate each month's share of the total Amount column in Power Query.

Calculate Each Month's Percentage of the Total in Excel 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.
Before you start

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.

Solution 1Recommended

Calculate monthly contributions with Power Query

Use Power Query to calculate the grand total and divide each monthly amount by it.

1
Create an Excel table

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.

2
Open Power Query

Select a cell in the table, then choose Data > From Table/Range.

3
Enter the query

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.

4
Load and format the results

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 with Power Query
Multiple rows per month: The query calculates a percentage for each source row. To show one result per month, use Home > Group By in Power Query to sum Amount by Month before calculating contributions.
Free Microsoft Office alternative

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and locate the Month and Amount columns.
  2. 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. 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.
Calculate monthly shares with a spreadsheet formula.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar interface for a smooth transition from Excel.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.