Niluthpal Chowdhury
POWER BI
SUMMARIZE
FUNCTION
Niluthpal Chowdhury
What is SUMMARIZE Function ?
In DAX SUMMARIZE function is used to create a
grouped table by summarizing data based on
the specified column.
If you have worked with SQL, you must be
familiar with the GROUP BY clause, which is used
to group results based on the specified columns.
SUMMARIZE function also does the same thing it
allows us to aggregate and return results for
each unique combination of the grouped
columns.
Niluthpal Chowdhury
Creating a table with SUMMARIZE
Let’s create a table with SUMMARIZE function to
better understand the usecases, We will see what
are the parameters required to create a
summarized table.
We will be creating a table something like this
which will hold City,Productname,Total Quantity,
UnitPrice and TotalSales.
Niluthpal Chowdhury
DATA-MODEL
Niluthpal Chowdhury
SYNTAX
SUMMARIZE (<table>, <groupBy_columnName>[,
<groupBy_columnName>]…[, <name>, <expression>]…)
Let’s start creating a table
Lets look into first part of the syntax which is table and
group by columnName
SUMMARIZE(FactInternetSales,DimProduct[EnglishProduc
tName],DimGeography[City])
In the first part of the syntax we need
to pass a table. In our case it is
FactInternetsales, then we need to pass
the column name for group by, we will
be passing two column name which is
city and productname becaue we need
our total sales to be shown on the basis
of City and Product Name.
This is the table we will get
Niluthpal Chowdhury
SUMMARIZE(FactInternetSales,DimProduct[EnglishProductNa
me],DimGeography[City],"Total
Quantity",SUM(FactInternetSales[OrderQuantity]),"Unit
Price",SUM(FactInternetSales[UnitPrice]),"Total
Sales",SUMX(FactInternetSales,FactInternetSales[OrderQuanti
ty]*FactInternetSales[UnitPrice]))
In the second part of the expression (marked in yellow) it will
take name arugument that we need to specify, it is a
summarized column. We will be showing Total Quantity so
we will pass a name “Total Quantity”, then we need to pass
an expression which will calculate Total Quantity which will
be SUM(factinternetsales[orderquantity]).
Likewise we will do the same for Unit Price and for Total
Sales we will use SUMX function which will go to sales table
and multiply orderquantity to unitprice row by row and
return the SUM of calculated values.
This will be
the Result
Niluthpal Chowdhury
Creating a measure with SUMMARIZE
Let say we need to create such a measure which can
return us a max value of Total Sales of each month by
city and product name.
And we need to show the
Max values of each month
in this matrix visual along
side Month Name
Niluthpal Chowdhury
If you look at the Data Model then you can see that both
city and productname column are comming from different
dimension table. So we will have to create a summarize
table and then we find the max values based on that table.
Let’s create a measure now
max total sales =
var a = SUMMARIZE(FactInternetSales,
DimGeography[City],DimProduct[EnglishProductName])
RETURN
MAXX(a,[Total Sales]) Lets Understand this Measure
At first we have created a summarized
table which is holding city and product
name and stored it in a variable named
‘a’.
In Return part we are using MAXX
function which takes table as a first
argument so there we have passed our
summarized table ‘a’ then it asks for an
expression where we have used Total
Sales measure. Here what MAXX will do
is that it will go to the table and look for
the each items and check the Total Sales
and will the return the highest value.
Niluthpal Chowdhury
Let’s Cross Check the Result
Measure Value Filtered Value
If we look Filtered Value visual where I have simply created
a table with city , productname and their total sales and
filtered data by 2006 and January.
We will see that the highest value is same as the measure
that we have created.
Niluthpal Chowdhury
IF YOU FIND THIS USEFUL THEN
LIKE
COMMENT
REPOST
And FOLLOW me for such
Content
