What does the aggregate function in mdx do when used on percent?

Question

What are the aggregate function in MDX do exactly when i apply it on a percentage column in SSAS or SSRS? I know it´s not the sum and i don´t think it´s average?

Answer 1

Aggregate function is not supposed to be used on top of calculations. I hope you are not retrieving percentage or ratio values from outside. Since such values are not aggregatable in the cube. For such Measures you need to retrieve the components and do the division inside the cube.

Aggregate function is fine to use for Measures you retrieve. For the measures you calculate in the cube, you need to handle time balance yourself. It is very difficult to advise more since each cube considerably different.

aggregate function does aggregation based on the aggregation property of measures retrieved.

For example, if a physical measure has aggregation property set to SUM, then it will sum. In addition, if you use account dimension (instead of using many measures) then it can also dynamically choose aggregation type. For example you can mark a certain account with SUM, and another one with last, it will dynamically aggregate even though you may have single Measure type with aggregation type = byaccount.

If you use aggregate function on a formula then the default behaviour to apply time balance to the measures you physically retrieve(formula components) and then evaluate the formula. So instead of evaluating the formula and applying time balance, it is applying time balance to components and then ignoring any time balance you possibly set(this part only valid for account type dimension).

Only in the case of account dimension you can set time balance property on a calculated member/derived cell(like parent), but still that property is ignored. This is the same problem like in Essbase. Engine just silently ignores it.