Subtotal From Base

Lets look at this report. This report has a metric defined as distinct count of subcategory. Do you find anything strange in this report ?

Yes, the grand total is 24 and not the sum 24 for three years. (72) . Here the count is a distinct records and is defined as Count(Subcategory){~+}

The subcategory for each of the Category have not changed in 3 years. Hence the count of distinct subcategory is 24. Finding a total for distinct count was not possible untill MicroStrategy version 9.3. With version 9.3 this is possible by a new feature called subtotal from base. You have to create this from the web as a derived metric by right click on an attribute. 

The distinct values cannot be reaggregated to higher levels, you have to aggregate it from the base level to get the correct count of distinct values. When used in a view report, the Cube Subsetting Instruction Engine generates the appropriate sql to calculate this distint count from the base level.

However you will not see the distinct count when you use a derived element. This is becase the derived element is calculated by analytical engine and it runs after CSI engine. Hence the data will be wrong, since analytical engine is not re aggregating using the base values. So instead of displaying wrong number, mstr would show "--" .  For example your report will look like below with derived element.

You will see similar results when you apply a metric limit, this is also because the metric limit is applied by the analutical engine after the CSI engine execution.