In this video, you’ll see a few examples of using SUMIF and COUNTIF in Power BI.
When Excel users come to Power BI, one of their first questions is “How do I use SUMIF?”
Turns out, there are no SUMIF and COUNTIF functions in Power BI. However, with the flexibility of DAX, of course, you can work around it using several different ways.
SUMIF
We remember that DAX is all about filter context. After you establish relationships between tables, any column with numbers can become a SUMIF. Simply drag two columns into a table, product and sales, for example, and filters will do the rest.
The second way to replace SUMIF is by using the CALCULATE function. In the video, we’re calculating the sum of sales for products, which cost less than $100. We use the following formula:
- Sales of units under $100 = CALCULATE (SUMX (Sales, [UnitPrice] * [SalesQuantity]), ‘Product’[UnitPrice] < 100)
COUNTIF
Now let’s take a look at COUNTIF. Again, the first option is possible thanks to the filters. In our example, we’d like to count the number of transactions per month. To do that we can simply put the transaction ID column into the table and select the count action instead of sum.
Another way is to create a measure using the COUNTROWS function:
- Count of transactions = COUNTROWS (Sales)
The function count the number of all rows in the sales table. Since each row in the table equals to a transaction, the sum of all rows gives the count of transactions.