Create Calculated Columns Using the DAX RELATED function create a calculated

# Create calculated columns using the dax related

• 309
• 100% (1) 1 out of 1 people found this document helpful

This preview shows page 276 - 280 out of 309 pages.

only show the date and not the time. Create Calculated Columns Using the DAX RELATED function, create a calculated column in the Product table for Product Subcategory and Product Category. [Category] =IF(ISBLANK([ProductSubcategoryKey]),"Misc", RELATED(Category[EnglishProductCategoryName])) [Subcategory] =IF(ISBLANK([ProductSubcategoryKey]),"Misc", RELATED(Subcategory[EnglishProductSubcategoryName]))Hide the Category and Subcategory tables from any client tools. If the ProductSubcategoryKey is blank, fill in the Category and Subcategory columns with "Misc" . Finally, create a hierarchy with the Product Category and Product Subcategory columns named Prd Cat . Create Measures Add the following measures to the ResellerSales table: Month Sales:=TOTALMTD(SUM([Sales Amount]),'Date'[Full Date]) Prev Month Sales:=CALCULATE(Sum([Sales Amount]),PREVIOUSMONTH('Date'[Full Date])) Monthly Sales Growth:=[Month Sales] - [Prev Month Sales] Monthly Sales Growth %:=DIVIDE([Monthly Sales Growth],[Prev Month Sales],0)
CHAPTER 14 CREATING A COMPLETE SOLUTION 279 Now you need to create a measure to determine if a store was open for at least a year for the month you are calculating the sales for. In the Reseller table, add the following measures: Years Open:=Year(FIRSTDATE('Date'[Full Date])) - Min([Year Opened]) Was Open Prev Year:=If([Years Open]>0,1,0) Figure 14-2. Viewing the measures in a pivot table Test your measures by creating a pivot table as shown in Figure 14-2 .
CHAPTER 14 CREATING A COMPLETE SOLUTION 280 Now you can combine these measures so that you are only including resellers who have been in business for at least a year at the time of the sales. Create the following measures: Month Sales Filtered:=Calculate([Month Sales],FILTER(Reseller,[Was Open Prev Year]=1)) Prev Month Sales Filtered:=CALCULATE([Prev Month Sales], Filter(Reseller,[Was Open Prev Year]=1)) Monthly Sales Growth Filtered:=[Month Sales Filtered]-[Prev Month Sales Filtered] Monthly Sales Growth Filtered %:=DIVIDE([Monthly Sales Growth Filtered], [Prev Month Sales Filtered],0) Figure 14-3. Testing the measures Create a pivot table to test your measures, as shown in Figure 14-3. Remember that the measures only make sense if you filter by year.
CHAPTER 14 CREATING A COMPLETE SOLUTION 281 Create the Dashboard Before creating a dashboard in Power View, you need to update some table settings to optimize the model for Power View. Table 14-3 lists the tables and settings you need to make. Figure 14-4. Testing the filtered measures Table 14-3. Settings to Optimize for Power View Table Name Row Identifier Default Label Default Photo Default Field Set Reseller ResellerKey Reseller Name Reseller Name, Reseller Type, Year Opened Product ProductKey Product Product Photo Product, Standard Cost, List Price, Dealer Price Location GeographyKey City, State Province, Country, Postal Code In addition, mark the columns listed in Table 14-4 as the appropriate data category.