Calculate Sum and Percentage Based on Another Column in Power Query: A Step-by-Step Guide

Are you tired of manually calculating sums and percentages in your data? Do you want to automate these calculations and make your life easier? Look no further! In this article, we’ll show you how to calculate sum and percentage based on another column in Power Query. By the end of this guide, you’ll be a pro at performing these calculations with ease.

What is Power Query?

Before we dive into the tutorial, let’s quickly introduce Power Query. Power Query is a powerful data manipulation tool in Excel and Power BI that allows you to transform, filter, and analyze your data with ease. It’s a game-changer for anyone working with data, and we’ll be using it extensively in this article.

Preparation is Key

Before we start, make sure you have the following:

• A Power Query-enabled application (Excel, Power BI, or Power Apps)
• A data table with at least two columns: one for the values you want to calculate and another for the column you want to base the calculation on
• A basic understanding of Power Query formulas and syntax (don’t worry, we’ll cover everything you need to know)

Calculate Sum Based on Another Column

Let’s say you have a table with sales data, and you want to calculate the total sales for each region. You can do this by creating a new column that sums up the sales values based on the region column.

Step 1: Load Your Data into Power Query

// Load your data into Power Query
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content]
in
    Source

Replace “Sales” with the name of your table or range.

Step 2: Group by Region and Calculate Sum

// Group by region and calculate sum
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grouped = Table.Group(Source, {"Region"}, {{"Total Sales", each List.Sum([Sales]), type number}})
in
    Grouped

In this step, we’re using the `Table.Group` function to group the data by the region column. The `each List.Sum([Sales])` part calculates the sum of the sales values for each group.

Step 3: Add the Calculated Column

// Add the calculated column
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grouped = Table.Group(Source, {"Region"}, {{"Total Sales", each List.Sum([Sales]), type number}}),
    Added_Column = Table.AddColumn(Grouped, "Region Total Sales", each [Total Sales])
in
    Added_Column

In this final step, we’re adding a new column called “Region Total Sales” to the original table, which contains the calculated sum for each region.

Calculate Percentage Based on Another Column

Now, let’s say you want to calculate the percentage of total sales for each region. We’ll build upon the previous example to achieve this.

Step 1: Calculate the Grand Total

// Calculate the grand total
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grand_Total = List.Sum(Source[Sales])
in
    Grand_Total

We’re calculating the grand total sales value, which we’ll use as the basis for our percentage calculation.

Step 2: Calculate the Percentage

// Calculate the percentage
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grouped = Table.Group(Source, {"Region"}, {{"Total Sales", each List.Sum([Sales]), type number}}),
    Grand_Total = List.Sum(Source[Sales]),
    Percentage_Column = Table.AddColumn(Grouped, "Region Percentage", each [Total Sales] / Grand_Total)
in
    Percentage_Column

In this step, we’re adding a new column called “Region Percentage” to the original table, which calculates the percentage of total sales for each region using the grand total as the basis.

Putting it All Together

Let’s combine the previous examples to create a comprehensive solution.

// Calculate sum and percentage based on another column
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grouped = Table.Group(Source, {"Region"}, {{"Total Sales", each List.Sum([Sales]), type number}}),
    Grand_Total = List.Sum(Source[Sales]),
    Added_Columns = Table.AddColumns(Grouped, {
        {"Region Total Sales", each [Total Sales]},
        {"Region Percentage", each [Total Sales] / Grand_Total}
    })
in
    Added_Columns

This final formula calculates both the sum and percentage of sales for each region and adds them as new columns to the original table.

Tips and Variations

Here are some additional tips and variations to help you take your calculations to the next level:

• Use `Table.Group` with multiple columns to create hierarchical groups.
• Use `List.Average` instead of `List.Sum` to calculate the average value.
• Use `List.Max` or `List.Min` to calculate the maximum or minimum value.
• Use `Table.AddIndex` to add a unique index column to your table.
• Use `Table.Sort` to sort your data by one or more columns.

Conclusion

In this article, we’ve shown you how to calculate sum and percentage based on another column in Power Query. By following these steps and examples, you should be able to automate your calculations and make your life easier. Remember to experiment with different formulas and functions to unlock the full potential of Power Query.

Column	Region	Sales	Region Total Sales	Region Percentage
1	North	100	500	20%
2	South	200	800	30%
3	East	300	1200	40%
4	West	400	1600	50%

This table demonstrates the expected output of our calculation, with the “Region Total Sales” and “Region Percentage” columns added to the original table.

Now, go forth and conquer your data calculations with Power Query!

Frequently Asked Question

Ready to unlock the power of Power Query? Dive into these FAQs to learn how to calculate sums and percentages based on another column!