Recreating Sum If In Power BI

In this article, you will see how to use DAX (Data Analysis Expressions) in Power BI to implement the Power BI Sum If function.

The Sum If function is used to sum values in a data column based on a specific condition.

It can be used to sum total profit obtained from sales of products if the product category is Technology or Furniture or both. 

By default, Power BI does not contain the Sum If function. You can, however, use DAX formulas to implement it; an example of which you will see in this article.

We’ll show you how to use the Sum If function to implement single as well as multiple conditions. 

To learn more about DAX, check out our Introduction to Dax article here.

If you’re looking for formal training in Power BI, check the available courses and see which one would suit you best.

Creating A Sample Report

Let’s create our sample report that you will be using to implement the Sum If function in Power BI. 

The sample data used to create the sample report is available as a CSV file at the following link:

https://raw.githubusercontent.com/curran/data/gh-pages/superstoreSales/superstoreSales.csv

The dataset contains various information on product sales for a fictional superstore between the years 2009 and 2012.

Using the above dataset, you will create a simple report with a slicer visualisation and a card visualisation. The slicer is used to filter data based on years, whereas the card displays the total profit for all the products. 

Two cards on Power BI. One showing a slicer with the years 2009 and 20012, and the other showing 1.52 M of total profit.

Creating Sum If With A Single Condition Using DAX

In this section, you will create the Power BI Sum If function with a single condition which returns the sum of profits from the sale of different products if the products category is ‘Technology’. 

To do so, click the three dots at the top right of your imported dataset in the Reports view, as shown in the screenshot below. 

Click the ‘More options’ label that appears. 

Visualisations and fields dialogue box with the three dots highlighted. More options label showing.

From the list of options, click the ‘New measure’ option as shown below. A measure can be used to implement DAX operations in Power BI.

More options context menu on Visualisations and Fields dialogue box. New measure highlighted.

You will see an empty text field appear below the top menu and at the top of the visuals pane in your reports view. Here is a screenshot for your reference:

Field highlighted showing 1 Measure =.

In the text field above, you can write your DAX function that implements the Sum If functionality. Our DAX function looks like the one in the following text box.

Category_Technology = CALCULATE(SUM(superstoreSales[Profit]),superstoreSales[Product Category]=“Technology”)

The name of our measure is ‘Category_Technology’. We use the CALCULATE operator to create a DAX function which sums all the values from the ‘Profit’ column of our ‘superstoreSales’ dataset. 

Next we specify the If condition which states that we want a sum of the profit only for those products where the ‘Product Category’ equals ‘Technology’.

Hitting the “Enter” button on your keyboard will compile your new measure for the Sum IF DAX function.

Dax function and data format highlighted.

Now if you look at the data fields, you will see that your new measure ‘Category_Technology’ is added to the list of columns.

To see this measure in action, create a new card visualisation and drag and drop the ‘Category_Technology’ measure under the ‘Fields’ option of your new card.  

Visualisations and Field dialogue box. The field Category_Technology is highlighted on both sides.

On your reports view, you can see two cards now. The one on the top right displays the total profit between 2009 and 2012 for products from all categories. 

The other one (bottom left), which uses the Power BI Sum If function, displays the total profit for the products in the Technology category.

You can also see the currency symbol being used in the card containing the Power BI Sum If function.

Three Power BI cards. One is a slicer with the years 2009 and 2012, the other card says 1.52M profit and the third one $886.31K Category_Technology.

You can further filter the records by year using the slicer.

For instance, the following figure shows the total profit and the profit for the Technology category between the years 2009 and 2010. 

Three Power BI cards. The first is a slicer showing the years 2009 and 2010, the second shows 798.41K Profit and the third $432.07K Category_Technology.

Creating Sum If With Multiple Conditions Using DAX

You can also use the Power BI Sum If function created via DAX to implement multiple conditions

As an example, the following DAX function creates a measure which takes the sum of profits for all the products in the Technology category, with an additional condition that the products are sold in the Ontario region

You can see that you can separate multiple conditions by simply using a comma ‘,’ in your DAX function. 

Category_Technology_Ontario = CALCULATE(SUM(superstoreSales[Profit]),superstoreSales[Product Category]=“Technology”, superstoreSales[Region]=“Ontario”)

Dax function highlighted.

Now create another card using the ‘Category_Technology_Ontario’ measure that you just created.

New field Category_Technology_Ontario highlighted.

In the output below, you can see another card added (bottom right) that shows the total profit for the Technology products sold in the Ontario region, between the years 2009, and 2010

Four Power BI cards showing a slicer with the years 2009 and 2009, a profit of 798.41K, sales of $432.07K in Category_Technology and $92.09K in Category_Technology_Ontario.

Acuity's Power BI training is hands down, the most engaging and informative training session I've ever attended. Highly reccomended.

Final Thoughts

The Power BI Sum If function is not by default available in Power BI. However, you can use DAX functions to implement the PowerBI Sum If function as you saw in this article. 

Though DAX is a useful way for implementing the Sum If function, you cannot use the DAX operations in the Power BI service. Hence, to publish a report containing the Sum If function using the PowerBI service, you should first implement the Sum If function in Power BI Desktop and then import the report to Power BI Service. 

 

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.