Welcome to Excel Jet Consult. In this tutorial, we will learn how to use the following DAX aggregator functions – COUNT, SUM, AVERAGE, MAX and MIN with ROLLUP Nested Inside SUMMARIZE DAX Function. Let’s get started.
- COUNT Function counts all the number in a column
- COUNT Syntax =COUNT(ColumnName)
- SUM Function adds all the numbers in a column
- SUM Syntax =SUM(ColumnName)
- AVERAGE Function returns the arithmetic mean of numeric values in a column
- AVERAGE Syntax =AVERAGE(ColumnName)
- MAX Function returns the largest numeric values in a column
- MAX Syntax =MAX(ColumnName)
- MIN Function returns the smallest numeric values in a column
- MIN Syntax =MIN(ColumnName
- ROLLUP Function identifies a subset of columns specified in the call to SUMMARIZE function that should be used to calculate Subtotal
- ROLLUP Syntax =ROLLUP(GroupBy_ColumnName1,…)
- SUMMARIZE Function returns a summary table for the requested totals over a set of groups
- SUMMARIZE Syntax =SUMMARIZE(table, [GroupBy_ColumnName1],…)
The first thing is to load the table into the data model which we have done.
In the caption below, we want to summarize the following: CountofSales, Total Sales, Average Sales, Best Sales and Worst Sales by Account Manager in a new column. Note, the name of our data is Transaction.
The Excel Approach
However, we want to use the same data in Excels
- Create a new sheet and renamed Report
- Copy all the Account Manager into the new sheet
- Remove Duplicate values and Sort in Ascending Order
- Provide the following as the heading names: CountOfSales, Total Sales, Average Sales, Best Sales and Worst Sales
- In the Data sheet: press CTRL + * and CTRL + SHIFT + F3 to select all and apply named range
- Check Top Row only (if not automatically checked)
- Click OK
- In cell B2 of Report sheet, execute the formula: =COUNTIFS(Account_Manager,[@[Account Manager]])
- In cell C2 of Report sheet, execute the formula: =SUMIFS(Total,Account_Manager,[@[Account Manager]])
- In cell D2 of Report sheet, execute the formula: =AVERAGEIFS(Total,Account_Manager,[@[Account Manager]])
- In cell E2 of Report sheet, execute the formula: =MAXIFS(Total,Account_Manager,[@[Account Manager]])
- In cell F2 of Report sheet, execute the formula: =MINIFS(Total,Account_Manager,[@[Account Manager]])
Note, MINIFS and MAXIFS functions are only available in Office 365 and Microsoft Office 2019 versions. In addition, the report table is stored in Excel using CTRL + T
- Select C2:F15 and execute CTRL + SHIFT + 4 to apply currency formatting
- Select A16:F16
- In the Styles group of the Cells Styles dropdown of the Home tab, select Total style
Calculated the Total for all Account Manager using COUNT, SUM, AVERAGE, MAX and MIN
Power BI Approach
Having seen the report in Excel, we want to replicate the same thing is Power BI. Thus:
Create New Table: in the Calculations group of the Modelling tab, click on New Table.
- Execute the indented DAX formula:
Sales By Account Manager = SUMMARIZE(
Transaction_facttbl,ROLLUP(Transaction_facttbl[Account Manager]
),”CountOfSales”,COUNT(Transaction_facttbl[Total]
),”Total Sales”,SUM(Transaction_facttbl[Total]
),”Average Sales”,AVERAGE(Transaction_facttbl[Total]
),”Best Sales”,MAX(Transaction_facttbl[Total]
),”Worst Sales”,MIN(Transaction_facttbl[Total]
)
)
Refer to the video for full explanation of each arguments in the above DAX formula
To apply currency formatting: select each column, click on the currency format dropdown and select desired currency
Next, we sorted the Account Manager column in Ascending Order
In the caption above, we have a standard report to make decision