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