1. a new table using SUMMARIZE: This table 'SalesOrderSummary' should include columns Order Number and Order Date from the 'DaxBook Sales' table and Customer Type from the 'DaxBook Customer' table. Besides, it creates a new column Order Sales by summing all Individual Sales Amount within an order (SUM('DaxBook Sales' [Individual Sales Amount])). The image to the right shows the partial data in the new table (where Customer Type Person ends and Company starts). You may want to reference the formula for Sales ByCountryand ChildTemp Table in "DAX basics examples-in-class.pbix". FYI: If you examine the data in the 'DaxBook Sales', you will find that the same order number may appear in several records (if you right-click the Order Number column, select 200701023CS425, then click OK, the table will show only the records that have the 200701023CS425 value of Order Number because the filter has been applied). Each record in the group of records that have the same order number represents a product ordered by a customer in the sales order. Each record in the group has a different Order Line Number. When we include Order Number, Order Date, and Customer Type in the SUMMARIZE function, the DAX engine groups the records by the values of Order Number, Order Date, and Customer Type, then perform the SUM calculation on the column [Individual Sales Amount] of all the records in the group of records that have the same values of Order Number, Order Date, and Customer Type. It essentially executes an SQL statement that INNER JOINS the two tables and includes the GROUP BY clause (the two tables are linked in the Model view, so the DAX engine knows how to join them). 2. a new table using GROUPBY: This table 'BrandUnit Cost' uses the Brand of the table 2000 5/200120000 Pe Co 2004 30064842000 4200 4/19/2007 12:00:00 A Com 30044204413/201200.00 A // 6/13/20 2004 30 www. Lapin Com 2000 company 30052400 Compr 3002242900 540/12000 Comp 2004200 3 Comp 022/2000 m AN 100 SUM SLINGSP C Menget 2004433/2004 pe 20076442000 435/2007 2:00:00 200764401004 Company SA 200444/3/20 2001/200220000 P 22/2009 200.00 www mpare Com pe 200224211522/2007 12:00:00 00 200441 5/13/200120000 AN HON 2004 Comp F S 08535 www ** *** Souvid Video Adventure Works Wide World importers Pertem Prevare Northind Traders A DONUME The Phone Company Lore Ser 3402 SUNON S S SUSA AFT 3402 Sara ME NO Dadlock Product BrandBand Aage Unit Co Ta Toys SUM SUS 'DaxBook Product' as the . The new column "Brand Average Unit Cost" is calculated using the formula AVERAGEX(CURRENTGROUP(), 'DaxBook Product'[Unit Cost]). NOTE: The CURRENTGROUP function is used as an argument to the function AVERAGEX, which tells the DAX engine to perform the AVERAGEX function for each brand group. We use AVERAGEX instead of AVERAGE because we calculate the average unit cost for each brand group, so the calculation is filtered. The final table data is shown to the right. FYI: SUMMERIZE and GROUPBY have similar syntax and take similar parameters, but internally they are different, which is an advanced topic and out of the scope of this class. 522.6857 3121 0349 5699459 TOMM ALLOW 5242713 5129 7062 $1256093 32031151
