Now that i am back from quite a good weekend, i thought i would blog about something which is pretty interesting i.e. Level based Measures. Lets try to understand this first. This is what the documentation has got to say about this “A level-based measure is a column whose values are always calculated to a specific level of aggregation. For example, a company might want to measure its revenue based on the country, based on the region, and based on the city. You can set up columns to measure CountryRevenue, RegionRevenue, and CityRevenue. “ As stated above Level based measures have their aggregation set based on a level in the hierarchy. Lets try to understand this using a simple example. We will start with a simple hierarchy as shown below.
Basically, we have a hierarchy and we would like to have the Sales measure aggregated over 3 levels i.e. Total Level, Region Level and the Country level. Now lets start with creating 3 logical measure columns Sales By Region, Sales By Country and Total Sales. Easch of these measures are exact replica of the Sales Measure which has a default aggregation of SUM.
Now, in order to make the 3 columns to aggregate over Region, Country and Total levels just drag and drop the corresponding columns to their associated levels as shown below.
Now, try creating a report with all the 4 columns(including the base SUM aggregated Sales measure) and see what happens.
As shown above what happens is Sales By Country calculates the sales across all the countries. Sales By Region calculates the sales across all the regions. And Total sales gives total sales across all the regions and countries. But you would see a lot of line items because of the granularity at which the Sales By Country Works. Since there are 17 different countries you would see 17 different line item sums. Now, if you remove the Sales By Country column the number of line items gets reduced to the number of regions i.e. 5
This could be a lot useful in situations where you want to a division of different LBM’s for example, Sales By Region/Sales By Country.
Posts
