Power BI Formula: Sum if - 3 columns

[gmasta1129]

Hello, 

I need help with creating a sum if formula where if "Margin Account Y/N?" column C is equal to "Y" then sum the values in column B "Margin Balance USD" by column A "Facility Code". 

Therefore, facility code 88888 should sum to $8,440,538 since there are two rows of data/values.  

[gmasta1129]

Hello @123abc , 

Thank you for the reply but I do not need this in excel, I need the formula done in Power BI. Also, I should mention that column B and C are calculated columns and not the original columns in the data.   

[123abc]

To create a SUMIF formula in Power BI where you want to sum values in column B ("Margin Balance USD") based on a condition in column C ("Margin Account Y/N?"), you can use DAX (Data Analysis Expressions) functions. In Power BI, this can be achieved using the SUMX function in combination with FILTER. Here's how you can create the measure:

1. Open Power BI Desktop and load your data into the data model.

2. In the "Model" view, click on "New Measure" in the "Modeling" tab to create a new measure.

3. In the formula bar that appears, enter the following DAX formula:

Total Margin Balance USD =
SUMX(
FILTER(
YourTableName, // Replace 'YourTableName' with the actual name of your table
YourTableName[Margin Account Y/N?] = "Y"
),
YourTableName[Margin Balance USD]
)

Make sure to replace 'YourTableName' with the actual name of your table that contains the data.

1. Press Enter to create the measure. This measure will calculate the sum of "Margin Balance USD" where the "Margin Account Y/N?" column equals "Y."

2. Now, you can add this measure to a visual, such as a card or table, to display the total margin balance for rows where "Margin Account Y/N?" is equal to "Y."

Here's how you can add this measure to a table:

• Create a table visual in your report.
• In the "Values" section of the table visual, drag and drop the "Total Margin Balance USD" measure you just created.

The table should now display the sum of "Margin Balance USD" for each "Facility Code" where "Margin Account Y/N?" is equal to "Y."

In your example, if there are two rows with "Facility Code" 88888 and "Margin Account Y/N?" equal to "Y," the total margin balance for that code will be displayed as $8,440,538.

[123abc]

You can achieve this using the SUMIFS function in Excel to sum values in Column B based on the criteria in Column C and Column A. Here's how you can create the formula:

Assuming your data is in columns A, B, and C, and you want to calculate the sum of "Margin Balance USD" (Column B) where "Margin Account Y/N?" (Column C) is equal to "Y," and group the results by "Facility Code" (Column A), you can use the following formula:

```excel
=SUMIFS(B:B, C:C, "Y", A:A, A2)
```

Here's a breakdown of the formula:

- `B:B`: This is the range containing the values you want to sum (Column B).

- `C:C`: This is the range containing the criteria you want to check (Column C).

- `"Y"`: This is the criteria you're looking for in Column C.

- `A:A`: This is the range containing the criteria you want to group by (Column A).

- `A2`: This is the specific Facility Code you want to match for the current row in your calculation. You can place this formula in another column (e.g., Column D) and copy it down for each row in your dataset.

After you've placed the formula in a new column (e.g., Column D), it will calculate the sum of "Margin Balance USD" for each Facility Code where "Margin Account Y/N?" is equal to "Y." Copy the formula down for each unique Facility Code, and you'll get the desired sums. In your example, you'll get $8,440,538 for Facility Code 88888.