Hi all,

On one cube, I need to create a particula measure (Pct_Departement).

In my fact table, I have a column contains ID of "Region" dimension (which contains "Alabama","Texas","Minesota"..., and one column "Indice" which contains values 0 or 1.

I need to create a measures (in %) : sum(Indice)/ count(Indice), but only use with "Region"
I try something like SUM([Region].[Region].allmembers,[Measures].[Indice])/Count([Region].[Region].allmembers,[Measures].[Indice]), but seems not working

help will be appreciate.

Thanks in advance,

Regards,

Hi,

use Scope()

like:

AS NULL;

SCOPE([Region].[Region].allmembers,[Measures].[YourMEasure]);

This=SUM([Region].[Region].allmembers,[Measures].[Indice])/Count([Region].[Region].allmembers,[Measures].[Indice]);

End scope;

Hi phoenix,

According to your description, you want to calculate the percentage of all indices. Right?

In Analysis Services, when using tuple inside of the count function, it returns the number of dimensions in a tuple, not the number of records. Please see: Count (Tuple) (MDX) . Since all the Regions contain values either 1 or 0, you can just count the set of the dimension members. So your expression can be like:

SUM([Region].[Region].allmembers,[Measures].[Indice])/Count([Region].[Region].allmembers)

If you want to calculate the percentage for each member, you can include current member in sum function and apply the sets based on axis:

(Axis(1).Item(1).Hierarchy.CurrentMember,[Measures].[Indice])/Count(Axis(1))