Person: In Looker, you can define measures to aggregate dimensions in your data, such as getting a count or average. In this example, imagine that your view already contains a dimension for sale price for ordered items, and you want to use it to calculate the total dollar amount of all ordered items. What kind of measure do you need to create? The key word total tells you that you need to create a measure of type sum. Let's see how easy it is to create a new measure to calculate a total of all sale prices for ordered items. To begin, enable development mode by toggling the button for development mode on the bottom left corner of the Looker home page. Then, click develop on the left side navigation menu. Within the develop options, click on the training_ecommerce project. Navigate to the order_items.view file in the training_ecommerce project. To make a new measure for total sales, scroll down to the last dimension in the order_items.view file and add a new blank line under the last dimension. This supports readability of the view file as all measures will be defined after the dimensions in the view. Using the same indentation as the existing dimensions and measures, start typing the word measure. Notice that as you write code, the Looker IDE will check your syntax in real time and make suggestions. Now, enter a colon after the word measure. You will see two curly braces appear. In the next steps, you will add code within these curly braces to tell Looker how this measure should be defined. Add a name for the new measure after the colon, following the word measure but before the curly braces. Similar to naming other LookML objects, we recommend using all lowercase letters and using underscores to represent spaces between words. In this case, total_sales would work well as the name of a new measure that you are creating for the total dollar amount of all ordered items. Next, between the curly braces on the following line, define the type of measure that you want with the key word type followed by a colon. Now, because you are calculating the total, you would need to choose the measure type sum. On another new line between the curly braces, add the SQL parameter by typing SQL followed by a colon. Notice that Looker adds two auto-generated semicolons at the end of the SQL parameter. Similar to defining dimensions, you need to add the desired SQL before these two auto-generated semicolons so that Looker knows where the SQL parameter ends. In this case, you can use this syntax to identify an existing dimension called sale_price by typing ${sale_price}. The SQL parameter combined with the type sum will result in a new measure that is a sum of all values in the sale_price dimension. Last, specify a format for the measure using the value_format_name parameter and the parameter value usd_0, which represents the currency format used in the United States rounded to the nearest dollar. Now, click save changes. Then, click validate LookML to check that you have written valid LookML to define your new measure. Now, you can test the new measure in the explorer to review what your business users will see. Within the explore tab, right-click on order items under the e-commerce training heading and select to the open explorer in a new tab. Notice that total sales is now a dimension under order items. You can select total sales and click run to see the results. And with that, you have now defined and tested a new measure in Looker. Pretty easy, right?
