Grand Total Dimensional Hierarchy Example

Use this example to learn how to use a grand total dimensional hierarchy with revenue.

If your product dimensional hierarchy contains TotalProducts (Grand Total level), Brands, and Products levels, and a Revenue column defined with a default aggregation rule of Sum, you can then create an AllProductRevenue logical column. The AllProductRevenue column uses Revenue as its source. Associate the AllProductRevenue column to the Grand Total level. Each query that includes the AllProductRevenue column returns the total revenue for all products. The value is returned regardless of any constraints on Brands or Products.

If you have constraints on columns in other tables, the grand total is limited to the scope of the query. For example, if the scope of the query asks for data from 2000 and 2021, the grand total product revenue is for all products sold in 2000 and 2021.

If you have three products, A, B, and C with total revenues of 100, 200, and 300 respectively, then the grand total product revenue is 600, the sum of each product's revenue. If you have set up a semantic model as described in this example, the following query produces the results listed:

SELECT product, productrevenue, allproductrevenue
FROM sales_subject_area
WHERE product IN ('A','B')			

The results are as follows:

PRODUCT;;PRODUCTREVENUE;;ALLPRODUCTREVENUE
A;;;;;;;;100;;;;;;;;;;;;;600
B;;;;;;;;200;;;;;;;;;;;;;600			

The AllProductRevenue column always returns a value of 600, regardless of the products on which the query constrains.