Base measures

Before any custom or complex DAX measures can be developed, a set of relatively simple base measures must be implemented first. These measures represent the metrics from the Define the facts section of Chapter 1, Planning Power BI Projects, and thus contain validated and approved business definitions. For Adventure Works, a set of 12 base measures related to sales, cost, and margins are applicable to both the Internet Sales and Reseller Sales fact tables, such as the following:

Reseller Gross Sales = SUMX('Reseller Sales',
    'Reseller Sales'[Unit Price]*'Reseller Sales'[Order Quantity])
Reseller Net Sales = [Reseller Gross Sales] - [Reseller Sales Discounts]
Reseller Sales Product Cost = SUMX('Reseller Sales',
'Reseller Sales'[Order Quantity]*'Reseller Sales'[Product Standard Cost])  
Reseller Cost of Sales = [Reseller Sales Product Cost] + [Reseller Sales Freight Cost]
Reseller Gross Product Margin = [Reseller Gross Sales] - [Reseller Sales Product Cost]
Reseller Gross Product Margin % = DIVIDE([Reseller Gross Product Margin],[Reseller Gross Sales])
Reseller Net Product Margin = [Reseller Net Sales] - [Reseller Sales Product Cost]
Reseller Net Product Margin % = DIVIDE([Reseller Net Product Margin],[Reseller Net Sales])
Reseller Gross Margin = [Reseller Gross Sales] - [Reseller Cost of Sales]
Reseller Gross Margin % = DIVIDE([Reseller Gross Margin],[Reseller Gross Sales])
Reseller Net Margin = [Reseller Net Sales] - [Reseller Cost of Sales]
Reseller Net Margin % = DIVIDE([Reseller Net Margin],[Reseller Net Sales])

As shown in the Fact table columns section from Chapter 3, Designing Import and DirectQuery Data Models, three fact table columns (Extended Amount, Sales Amount, and Total Product Cost) were excluded from the Power BI fact table to save resources. The SUMX() function is used to compute the equivalent values from these three columns to support the Gross Sales, Net Sales, and Product Cost measures, respectively.

Sales discounts and freight costs, both simple sums of their respective fact table columns, are the two measures that create differences among the base measures. Discounts separate gross sales from net sales and freight costs separate the cost of sales from product costs only. The distinct definitions of the base measures support common analysis needs, such as the profitability (margin) of sales inclusive or exclusive of freight costs.

With base measures created for both Reseller Sales and Internet Sales fact tables, an additional set of base measures can be created for Adventure Works as an organization. Several of these measures can simply sum the Reseller Sales and Internet Sales measures as shown in the following examples:

AdWorks Net Sales = [Internet Net Sales] + [Reseller Net Sales] 
AdWorks Cost of Sales = [Internet Cost of Sales] + [Reseller Cost of Sales]
AdWorks Net Margin = [AdWorks Net Sales] - [AdWorks Cost of Sales]
AdWorks Net Margin % = DIVIDE([AdWorks Net Margin],[AdWorks Net Sales])

Additional DAX measures with specific filtering or evaluation logic such as date intelligence metrics can reference the base measures in their expressions. Via this measure branching, any subsequent changes to the definition of the base measures will be automatically reflected in other dependent measures. Additionally, the readability of the custom measures is improved, as these expressions only contain their specific logic.

Table of Contents for Base measures

Create new playlist

Sign In

Sign Up

Table of Contents for
Base measures