Articles   \  

Ratio Over Subtotals with Normalized Tables in DAX



Calculating a ratio in DAX is relatively simple in case the underlying data model is a star schema, but you have to consider additional complexities whenever you have a more normalized model, even just a snowflake schema. In this article, you will see how to perform the right computation.

Consider the following requirement: in Adventure Works DW, you need to query all the products belonging to a selection of product models and, for each product, you want to see the category it belongs to and the ratios of Sales Amount over the corresponding product model and product category. The model is a column of the Product Table (Model Name), whereas the category is a column of a related table (Product Category, related to Product through an intermediate Product Sub-Category table).

The initial DAX query can be the following one, that displays Product Name, Model Name and Category Name for each product of model Sport-100 and Road-650, which belong to Bikes and Accessories categories, respectively:

EVALUATE
CALCULATETABLE( 
    SUMMARIZE( 'Internet Sales',
               Product[Product Name],
               Product[Model Name],
               'Product Category'[Product Category Name],
               "Product Amount", SUM( 'Internet Sales'[Sales Amount] )
    ),
    FILTER( Product, 
            Product[Model Name] = "Sport-100"
            || Product[Model Name] = "Road-650" )
)
ORDER BY Product[Product Name]

In order to calculate the ratio of each product over the corresponding model and category, you need to obtain the value of Sales Amount for the model and the category in each row. The Model Amount is pretty straightforward, because it can be calculated by using the ALLEXCEPT function, which keeps just the filter on Model Name over the Product table. By filtering the Product table, you override the existing filter over products and obtain the required value.

MEASURE Product[Model Amount] 
    = CALCULATE( SUM( 'Internet Sales'[Sales Amount] ), 
                 ALLEXCEPT( Product, Product[Model Name] ) )

You cannot use the same approach for the category. The Product Category Name is a column of a the Product Category table, which is related to Product through the Product Sub-Category table. Because ALLEXCEPT can operate on just one table, we need to filter all the products belonging to the same Category in another way. The first approach is by using a FILTER condition that returns all those Product that belongs to the same category by using a CONTAINS condition.

MEASURE Product[Category Amount] 
    = CALCULATE( 
          SUM( 'Internet Sales'[Sales Amount] ), 
          ALL( 'Internet Sales' ),
          FILTER( ALL( Product ), 
                  CONTAINS( RELATEDTABLE( 'Product Category' ),
                            'Product Category'[Product Category Name],
                            VALUES( 'Product Category'[Product Category Name] ) ) ) )

The ALL( ‘Internet Sales’ ) is required in order to consider all the products of the same Category even if they belongs to other models (remember that there is a filter over Model Name in the initial query). The measure above produces the expected result, but it has two strong requirements: first, it requires an iterative FILTER operation over all the products; second, it requires that Product Category Name column of Product Category is part of the columns grouped by the SUMMARIZE function in the initial query. In order to avoid both constraints, a better approach can be used, even if it is less intuitive.

Instead of filtering the Product table with an explicit filter, you can leverage on filter contexts that are automatically propagated through relationships by using a function that returns just the Product Category that you need to filter. For example, you might define this measure by using VALUES over Product Category Name:

MEASURE Product[Category Amount] 
    = CALCULATE( 
          SUM( 'Internet Sales'[Sales Amount] ), 
          ALL( 'Internet Sales' ),
          VALUES( 'Product Category'[Product Category Name] ) )

Such a definition will work our initial query that contains the Product Category Name in its results, as you can see in the following example.

DEFINE
    MEASURE Product[Model Amount] 
        = CALCULATE( 
              SUM( 'Internet Sales'[Sales Amount] ), 
              ALLEXCEPT( Product, Product[Model Name] ) )
    MEASURE Product[Category Amount] 
        = CALCULATE( 
              SUM( 'Internet Sales'[Sales Amount] ), 
              ALL( 'Internet Sales' ),
              FILTER( ALL( Product ), 
                      CONTAINS( RELATEDTABLE( 'Product Category' ),
                                'Product Category'[Product Category Name],
                                 VALUES( 'Product Category'[Product Category Name] ) ) ) )
EVALUATE
    CALCULATETABLE( 
        SUMMARIZE(
            'Internet Sales',
            Product[Product Name],
            Product[Model Name],
            'Product Category'[Product Category Name],
            "Product Amount", SUM( 'Internet Sales'[Sales Amount] ),
            "Model Amount", [Model Amount],
            "Category Amount", [Category Amount]
        ),
        FILTER( Product, 
                Product[Model Name] = "Sport-100"
                || Product[Model Name] = "Road-650" )
    )
ORDER BY Product[Product Name]

The query above produces the following result:

Product[Product Name] Product[Model Name] Product Category[Product Category Name] [Product Amount] [Model Amount] [Category Amount]
Road-650 Black, 44 Road-650 Bikes 47566.6422 645379.5038 28318144.6507
Road-650 Black, 48 Road-650 Bikes 45553.2394 645379.5038 28318144.6507
      645379.5038 28318144.6507
Road-650 Red, 62 Road-650 Bikes 57381.9812 645379.5038 28318144.6507
Sport-100 Helmet, Black Sport-100 Accessories 72954.15 225335.6 700759.96
Sport-100 Helmet, Blue Sport-100 Accessories 74353.75 225335.6 700759.96
Sport-100 Helmet, Red Sport-100 Accessories 78027.7 225335.6 700759.96

However, if you remove the Product Category Name column from the SUMMARIZE output, the Category Amount measure definition would raise an error because it would try to perform a CONTAINS operation over all the product categories that are included in products filtered by the query, whereas the CONTAINS requires just one value to operate.

Because you need to obtain the Product Category from the “current” product for every line, you can use another SUMMARIZE call, which uses the Product table as a starting point to group data and returns just the Product Category Name as a result. Because this measure will be evaluated for every row of the complete DAX query, only the categories that are referenced by products considered in the output row will be considered, and if the output row contains just one product, you are sure that only its corresponding categories will be used.

MEASURE Product[Category Amount] 
    = CALCULATE( 
          SUM( 'Internet Sales'[Sales Amount] ), 
          ALL( 'Internet Sales' ),
          SUMMARIZE( Product, 'Product Category'[Product Category Name] ) )

With the correct Category Amount you can now calculate the corresponding ratios, as you can see in the following final query.

DEFINE
    MEASURE Product[Product Amount] = SUM( 'Internet Sales'[Sales Amount] )
    MEASURE Product[Model Amount] 
        = CALCULATE( 
              SUM( 'Internet Sales'[Sales Amount] ), 
              ALLEXCEPT( Product, Product[Model Name] ) )
     MEASURE Product[Category Amount] 
        = CALCULATE( 
              SUM( 'Internet Sales'[Sales Amount] ), 
              ALL( 'Internet Sales' ),
              SUMMARIZE( Product, 'Product Category'[Product Category Name] ) )
EVALUATE
    CALCULATETABLE( 
        SUMMARIZE(
            'Internet Sales',
            Product[Product Name],
            Product[Model Name],
            "Product Amount", [Product Amount],
            "Model Amount", [Model Amount],
            "Category Amount", [Category Amount],
            "Ratio Model", [Product Amount] / [Model Amount],
            "Ratio Category", [Product Amount] / [Category Amount]
        ),
        FILTER( Product, 
                Product[Model Name] = "Sport-100"
                || Product[Model Name] = "Road-650" )
    )
ORDER BY Product[Product Name]

This is the result produced.

Product[Product Name] Product[Model Name] [Product Amount] [Model Amount] [Category Amount] [Ratio Model] [Ratio Category]
Road-650 Black, 44 Road-650 47566.6422 645379.5038 28318144.6507 0.0737 0.0017
Road-650 Black, 48 Road-650 45553.2394 645379.5038 28318144.6507 0.0706 0.0016
    645379.5038 28318144.6507    
Road-650 Red, 62 Road-650 57381.9812 645379.5038 28318144.6507 0.0889 0.0020
Sport-100 Helmet, Black Sport-100 72954.15 225335.6 700759.96 0.3237 0.1041
Sport-100 Helmet, Blue Sport-100 74353.75 225335.6 700759.96 0.3300 0.1061
Sport-100 Helmet, Red Sport-100 78027.7 225335.6 700759.96 0.3463 0.1113

It is interesting to note that in DAX you have to write different queries according to the underlying data model in case you want to obtain values of a group for which an item belongs to. This is not required when working with MDX, even with the same data model, as you can see in the following query.

WITH 
    MEMBER Measures.[Model Amount] 
        AS ( Measures.[Internet Total Sales],
             [Product].[Product Name].[All] )
    MEMBER Measures.[Category Amount] 
        AS ( Measures.[Internet Total Sales],
             [Product].[Product Name].[All],
             [Product].[Model Name].[All] )
    MEMBER Measures.[Ratio Model] 
        AS Measures.[Internet Total Sales] / Measures.[Model Amount], FORMAT="Percent"
    MEMBER Measures.[Ratio Category] 
        AS Measures.[Internet Total Sales] / Measures.[Category Amount], FORMAT="Percent"
SELECT 
    { Measures.[Internet Total Sales],
      Measures.[Model Amount],
      Measures.[Category Amount],
      Measures.[Ratio Model],
      Measures.[Ratio Category] } ON 0,
    NonEmpty( [Product].[Product Name].[Product Name].MEMBERS
              * { [Product].[Model Name].[Sport-100], [Product].[Model Name].[Road-650] }
              * [Product Category].[Product Category Name].[Product Category Name],
              Measures.[Internet Total Sales] ) ON 1
FROM [Internet Sales]

In MDX the calculation of Model Amount and Category Amount does not require different formulas and is not affected from the underlying data model. This is an important difference between these two languages. The flexibility of DAX, which allows more flexibility over calculation even when relationships are not defined in the data model, comes at the cost of requiring a greater knowledge about the data model in order to define the correct and more efficient DAX query.







 
Want to read more?