Mastering Power BI Calculations: A Step-by-Step Guide to Calculating a New Column

Are you tired of scratching your head trying to figure out how to calculate a new column in Power BI that sums the values of a column from Table1 and then subtracts the sum of groups of rows in Table 2? Look no further! In this comprehensive guide, we’ll take you by the hand and walk you through the process, step-by-step, to help you become a Power BI calculation master.

Before We Dive In…

Before we begin, let’s set the stage with a brief overview of the problem we’re trying to solve. Imagine you have two tables in Power BI:

Table 1
ID	Value
1	10
2	20
3	30

Table 2
ID	Category	Value
1	A	5
2	A	10
3	B	20
4	C	15

Your task is to create a new column in Table 1 that sums the values of the “Value” column and then subtracts the sum of groups of rows in Table 2 based on the “Category” column. Sounds daunting, but trust us, it’s easier than you think!

Step 1: Create a Calculated Column in Table 1

To start, let’s create a new calculated column in Table 1. Click on the “Modeling” tab in the ribbon and then click on “New Column” in the “Columns” group.

In the formula bar, enter the following formula:

New Column = SUM('Table 1'[Value])

This formula simply sums the values in the “Value” column of Table 1.

Step 2: Create a Calculated Table for Table 2

Next, we need to create a calculated table for Table 2 that groups the rows by the “Category” column and sums the “Value” column. Click on the “Modeling” tab in the ribbon and then click on “New Table” in the “Tables” group.

In the formula bar, enter the following formula:

Grouped Table 2 = GROUPBY('Table 2', 'Table 2'[Category], "Sum of Value", SUMX('Table 2', 'Table 2'[Value]))

This formula groups the rows in Table 2 by the “Category” column and then sums the “Value” column for each group.

Step 3: Create a Calculated Column in Table 1 (Again!)

Now, let’s create another calculated column in Table 1 that subtracts the sum of groups of rows in Table 2 from the sum of the “Value” column in Table 1. In the formula bar, enter the following formula:

Final Column = 'Table 1'[New Column] - SUMX('Grouped Table 2', 'Grouped Table 2'[Sum of Value])

This formula takes the sum of the “Value” column in Table 1 (calculated in Step 1) and subtracts the sum of groups of rows in Table 2 (calculated in Step 2).

The Final Result

And that’s it! You should now have a new column in Table 1 that sums the values of the “Value” column and then subtracts the sum of groups of rows in Table 2 based on the “Category” column.

ID	Value	Final Column
1	10	0
2	20	10
3	30	15

As you can see, the “Final Column” subtracts the sum of groups of rows in Table 2 from the sum of the “Value” column in Table 1, resulting in the correct calculation.

Tips and Variations

Here are a few additional tips and variations to keep in mind:

• You can modify the formula in Step 2 to group by multiple columns in Table 2 by adding more columns to the GROUPBY function. For example:

  
  Grouped Table 2 = GROUPBY('Table 2', 'Table 2'[Category], 'Table 2'[Subcategory], "Sum of Value", SUMX('Table 2', 'Table 2'[Value]))
  

• You can use the FILTER function to filter the rows in Table 2 before grouping and summing. For example:

  
  Grouped Table 2 = GROUPBY(FILTER('Table 2', 'Table 2'[Value] > 10), 'Table 2'[Category], "Sum of Value", SUMX('Table 2', 'Table 2'[Value]))
  

• You can use the ALL function to remove any filters that may be applied to Table 2 before grouping and summing. For example:

  
  Grouped Table 2 = GROUPBY(ALL('Table 2'), 'Table 2'[Category], "Sum of Value", SUMX('Table 2', 'Table 2'[Value]))
  

By following these steps and tips, you should be able to calculate a new column in Power BI that sums the values of a column from Table1 and then subtracts the sum of groups of rows in Table 2. Happy calculating!

Conclusion

In conclusion, calculating a new column in Power BI that sums the values of a column from Table1 and then subtracts the sum of groups of rows in Table 2 is a straightforward process that requires a little creativity and patience. By following the steps outlined in this guide, you should be able to master this calculation and take your Power BI skills to the next level.

Remember to practice, practice, practice, and don’t be afraid to experiment with different formulas and techniques. And if you get stuck, don’t hesitate to reach out to the Power BI community for help.

Happy Power BI-ing!

This article is optimized for the keyword “How do I calculate a new column in power bi that sums the values of a column from Table1 then subtracts the sum of groups of rows in Table 2?”.

Here are 5 Questions and Answers about calculating a new column in Power BI that sums the values of a column from Table1 then subtracts the sum of groups of rows in Table2:

Frequently Asked Question

Got stuck with calculations in Power BI? Worry no more! Here are some answers to your burning questions.