About Level-Based Measure Calculations

A level-based measure is a column whose values are always calculated to a specific level of aggregation.

You can set up columns to measure CountryRevenue, RegionRevenue, and CityRevenue. For example, a company might want to measure its revenue based on the country, region, and city.

When a query containing a presentation hierarchy includes a level-based measure column, and the query grain is higher than the level of aggregation specific to the column, the query results return null. If the request only contains ordinary columns and no hierarchical columns, the level-based measure isn't replaced with null.

You can create an AllProductRevenue measure as a level-based measure at the Grand Total level. Level-based measures allow a single query to return data at multiple levels of aggregation. Level-based measures are also useful in creating share measures, calculated by taking some measure and dividing it by a level-based measure to calculate a percentage. For example, you can divide salesperson revenue by regional revenue to calculate the share of the regional revenue each salesperson generates.

For example, to set up these calculations, you need to build a dimensional hierarchy in your semantic model that contains the Grand Total, Country, Region, and City levels. This hierarchy contains the metadata that defines a one-to-many relationship between Country and Region and a one-to-many relationship between Region and City. For each country, there are many regions, but each region is in only one country. Similarly, for each region, there are many cities, but each city is in only one region.

After building a dimensional hierarchy, you need to create three logical columns one each for CountryRevenue, RegionRevenue, and CityRevenue. The columns use the Revenue logical column as its source. The Revenue column has a default aggregation rule of SUM and has sources in the underlying databases.

Assign the CountryRevenue, RegionRevenue, and CityRevenue columns to the Country, Region, and City levels, respectively. Each query that requests one of these columns returns the revenue aggregated to its associated level.