Aggregate using “summarize”

In DAX we have a function called “summarize”. This function aggregates totals over a set of groups.

Here is an example on how this function can be used:

 
EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", SUM ( 'Internet Sales'[Sales Amount] ),
    "Quantity", SUM ( 'Internet Sales'[Order Quantity] )
)

The code above will give you sales and quantity per month per year. The first argument is the table name. The second and third argument is the columns we want to order by. Fourth and fifth argument is the numbers you want to aggregate.

If you want to use some of the predefined measures you can skip the sum and write like this:

EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Quantity", 'Internet Sales'[Internet Total Units]
)

If you manually want to set the order by clause this can be set like this:

EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Quantity", 'Internet Sales'[Internet Total Units]
)
ORDER BY 'Date'[Month Name], 'Date'[Calendar Year]

The MDX that will give you the same result can look like this:

SELECT NON EMPTY [Date].[Calendar Year].[Calendar Year] * [Date].[Month Name].[Month Name] ON 1,
{ [Measures].[Internet Total Sales], [Measures].[Internet Total Units] } ON 0
FROM [Internet Operation]

And the SQL to get the similar resulat might look like this:

SELECT dimdate.calendaryear, 
       dimdate.englishmonthname, 
       Sum([salesamount])   AS 'Internet Total Sales', 
       Sum([orderquantity]) AS 'Internet Total Units' 
FROM   [factinternetsales] 
       INNER JOIN dimdate 
               ON dimdate.datekey = [factinternetsales].[orderdatekey] 
GROUP  BY dimdate.calendaryear, 
          dimdate.monthnumberofyear, 
          dimdate.englishmonthname 

The result will be like this:

Summarize

Advertisements