Measures are created from aggregation (numeric) type columns in a fact table. There are some complex measures that might require data from related dimensions for filtering or grouping. The first example in this series was a summation for Sales. The measure is called Total Sales. This is the most common measure and it was created in the beginning in order to give context to the changes made in the dimension tables.
This article will expand on the simple summation and calculate a profit associated with the sales as well as some ratios. Remember, the grain of the Sales fact table is line item in a sale. There are times where the fact table will be an aggregation of data by the dimension keys. That is normal dimensional modeling. An example is a table where the aggregation is taken in the ETL but occasionally developers create a view for the aggregation.
Sales Fact
The first step with the fact table will be to clean it up like the dimension tables in the previous article. It was not included in the previous article because it follows a different pattern. In fact, all columns should be hidden except the ones that might slice and dice data. The columns not used as measures, relationships to dimensions or slicers will be deleted. Do not worry. If a column is deleted and needs to be added back, that can be done in the PowerQuery editor.
The columns Description, Package, Total Dry Items, Total Chiller Items and Lineage Key will be deleted. The Description and Package are available in the Stock dimension. Including these as slicers for the fact table could cause performance problems once the sales grow over time. If these columns can have a different value than in the Stock Item dimension, they need to be in a new dimension with a surrogate key or Stock Item needs to be a Slowly Changing Dimension The differences are handled in the ETL.
The rest of the columns are in 2 groups: surrogate keys and potential measure columns. Both of these groups can be hidden and still be used for measures and relationships.
Surrogate Keys
Figure 1 shows the surrogate keys in the Sales fact table.
Figure 1 Sales Table Surrogate Keys
These columns will be hidden because deleting them removes the relationships between the dimension tables. The WWI Invoice ID is included in the hidden columns. It is part of the natural key to each row in Sales.
The rest of the columns are the potential measure columns. They include columns (Figure 2) like Total Including Tax, Quantity, Unit Price, Profit, etc. These will be hidden as well. They are not deleted because they are used to build measures.
Figure 2 Potential Measure Columns
By using Analyze in Excel (Figure 3), the Sales table will now only have the Total Sales measure like Figure 4. The Total Sales measure was created in Level 3.
Figure 3 Analyze in Excel
The client now only sees (Figure 4) the measures created for the Data Mart.
Figure 4 Analyze in Excel – Sales Measure(s)
Creating Measures
Measures can be created with a tool button or by entering DAX. Figure 5 shows the Total Sales measure.
Figure 5 Total Sales Measure
The new measure created will be Total Profit. From Total Profit, the Gross Profit Margin ratio can be extracted. Figure 6 is showing the Total Profit measure plus where to change the display to Currency in the Properties. The DAX formula is SUM(Sales[Profit]). SUM is the function and Sales[Profit] is the table name and columns used to be summed. The sum is done for the rows filtered (it could be all columns) in the Sales table and summing one column: Profit.
Figure 6 Total Profit
In Figure 7, Gross Profit Margin (GPM) is a measure of Profit divided by Sales. The DIVIDE DAX function is used to handle divide by zero errors. Figure 8 shows the new ratio and the Format changed to Percentage.
The Gross Profit Margin has the DAX function DIVIDE with first the numerator [Total Profit] measure followed by the measure [Total Sales] (denominator) separated by a comma. If [Total Sales] is zero based on the filtered rows sent to the measure, the DAX function will return a zero. The Gross Profit Margin measure can be renamed to GPM by changing the Name in the properties are overwriting the name in the measure calculation.
Figure 8 Gross Profit Margin (GPM) Measure
Before analyzing in Excel, the ratio can be used as a KPI – Key Performance Indicator. Right-click the measure and select Create KPI… (Figure 9)
Figure 9 Create KPI
Figure 10 shows the Key Performance Indicator screen. This example will use an absolute value of 0.431 with a low of 0.415 and a high of 0.441. These values are used in order to show a different set of indicators with this data set. The numbers normally will come from the business department. The indicators will be a red diamond for below low value (bad), a yellow triangle for around the absolute value (OK) and a green circle to say the ratio is above the high (good).
Figure 10 KPI screen
Figure 11 shows analyzing the measures in Excel. The columns are sliced by the Date Hierarchy Y-Q-M-D and the rows are sliced by State Province. Notice also that there is a KPI grouping in the Excel PivotTable Fields area. So once a KPI is created, a new grouping is available in client tools between the Measures and Dimension attributes.
Figure 11 Excel Pivot Table of GPM and KPI Indicator
Summary
Even though these are simple measures, the DAX functions SUM and DIVIDE were used as a starting point. Just like dimensions, the fact table needs to be cleaned up for the reporting tools. The addition of hiding the numeric columns but not deleting them shows that the true place to have these values are in the Measures. This is really done to control the aggregation specified in the requirements. It also removes the slicing and dicing to affect the calculations. As a bonus, a KPI was added to the ratio Gross Profit Margin.
