# Same Result, Different Operations: Excel and PowerBI

Welcome to Excel Jet Consult blog post. In this blog post, I covered how to achieve same result in Excel and PowerBI using a different operation.

Video

Sample Data

In the screenshot below, we have the sample dataset for this tutorial. In the dataset, we have columns from Date to Sales Amount. What we want to do essentially is extract the unique Brand in column J. Thereafter, we are going to use the COUNTIFS, SUMIFS and AVERAGEIFS Functions to calculate the count, sum and average sales based on the Brand criteria.

Extract Unique Brand and Use COUNTIFS, SUMIFS & AVERAGEIFS Functions

• In cell J2, execute the formula: =SORT(UNIQUE(Sales[Brand]))
• In cell K2, execute the formula: =COUNTIFS(Sales[Brand],J2#)
• Click Enter
• In cell L2, execute the formula: =SUMIFS(Sales[Sales Amount],Sales[Brand],J2#)
• Click Enter
• In cell M2, execute the formula: =AVERAGEIFS(Sales[Sales Amount],Sales[Brand],J2#)
• Click Enter

Note, we used the # in the countifs, sumifs and averageifs formulas. This # sign works in M365 dynamic arrays formulas only

In the caption below, we have the final result for the single criteria formulas in Microsoft Excel

PowerBI with SUMMARIZE DAX Function

We want to achieve the same result in PowerBI as we did in Excel. Note that, the same dataset has been loaded to the PowerBI Desktop. Therefore,

Click on New Table

In the formula bar, we are going to use the SUMMARIZE DAX Function.

• Execute the following DAX Formulas

Sales Report =SUMMARIZE(Sales,

Sales[Brand],

“Count”,COUNT(Sales[Units]),

“Total Sales”,SUM(Sales[Sales Amount]),

“Average Sales”,AVERAGE(Sales[Sales Amount])

)

Click on Enter.

From the screenshot below, we have the final result

See you in the next tutorial

Posted in Blog.