Calculating percentage shares

Overview

This article explains how to add a column that shows the sales value for each product as a percentage of the grand total of sales of all products shown.

Steps

1. Create a GrandTotal value

• To start, click inside the table in the Excel worksheet
• Click the From Table/Range icon on the Power Query tab in Excel to create a new query. The Query Editor window will open.
• Click the Group By button on the Home tab in the Query Editor toolbar to open the Group By dialog.
• Click the minus button so that no columns are shown in the Group By section.
• Enter the text GrandTotal in the New Column Name box, select Sum in the Operation drop-down box, and select Sales in the Column drop-down box.

The dialog should be as shown in the figure below:

• Click OK to close the dialog.

The Query Editor will now show a table containing a single column called GrandTotal and a single value, which is the sum of all of the values in the Sales column in the original table.

• Right-click this value and select Drill Down, as shown in the figure below, and a new step will be created that shows this grand total as just a value and not a value in a table.

The output of the previous step will be as shown below, and this will be the grand total value you need to divide all of the original sales values by, to calculate the percentage.

2. Create a custom column

• Now retrieve the original sales values to perform the calculation.
• Click the ƒx button next to the formula bar (highlighted in the figure above) to create a new step and delete all of the code to the right of the equals sign in the formula bar;
• Replace it with the name of the first step in your query, which should be Source. This means the step will return the original table of data loaded from Excel, as shown below.

• Click the Custom Column button on the Add Column tab in the Query Editor toolbar and the Insert Custom Column dialog will appear.
• Call the new column Share. Double-click the Sales column to add it to the Custom column formula box and then divide that value by the GrandTotal value returned by the result of the drilldown.
• The Custom column formula box should contain the following code:

    = [Sales]/GrandTotal

The full configuration can be seen below:

You should now have a custom column with the raw percentage values, as shown below:

• Click the Close & Load button to close the Query Editor.

In the output table in the workbook you can format the values in the Share column as percentages in the usual way by selecting the column and selecting Format Cells from the right-click menu.

The final table, with the percentage shares should be as shown below!

3. The ‘M’ code

The M code for the entire query is given below:

let
    //Load data from the Excel worksheet
    Source = Excel.CurrentWorkbook(){[Name="Fruits"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Sales", Int64.Type},
    //Return a table containing the grand total of sales
    #"Grouped Rows" = Table.Group(#"Changed Type", {}, {{"GrandTotal", each List.Sum([Sales]), type nullable number}}),
    //Returns the grand total as a value on its own
    GrandTotal = #"Grouped Rows"{0}[GrandTotal],
    //Fetch the original input table
    Custom1 = Source,
    //Insert a custom column to calculate the percentage share
    #"Added Custom" = Table.AddColumn(Custom1, "Share", each [Sales]/GrandTotal),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Share", Percentage.Type}})
in
    #"Changed Type1"