Result for SUMMARIZE function will be a calculated table. It will create a new table based on set up rules.
It allows to design an aggregate table so that we can specifically pick up columns we want and do calculations on value field.
Syntax:
SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)
We are providing table name and column name want to group by in new table with some Expression for any newly created column with name defined for that field.
To demonstrate this function, we have three files Customer, Product and Sales. Files are loaded in Power BI Desktop.
Here my requirement is to add a new calculated table using SUMMARIZE DAX function with distinct Customer name and respective order quantity. Customer name is present in Customer table and Order quantity field present in Sales table.
Customers table:
Product Table:
Sales Table:
We have already set up relationship between tables:
Go to Modelling menu > select New Table
Start writing formula in formula bar:
Summarize Table = SUMMARIZE(Customers,Customers[FirstName],“Cal Order Quantity”,SUM(Sales[OrderQuantity]))
A new Table is created with Distinct Customer name and order quantity.
We can add more than one expression for adding new column in table >
Add one more column name “Revenue” for Customer name using Order quantity from Sales table and Price from Product table:
Summarize Table = SUMMARIZE(Customers, Customers[FirstName],”Cal Order Quantity“,SUM(Sales[OrderQuantity]),”Revenue“,SUMX(Sales,Sales[OrderQuantity]*RELATED(Products[ProductPrice])))
Create a visualization:
