Hi,

Please can someone explain how I can get around this problem?

Score / Number of accounts

is not working as many Score values are NULL

The 'number of accounts' is including NULL score rows which is lowering the
score when looking at high level data within a pivot table.

I've tried all these and none of them work
1) IIF([Measures].[Number of Accounts] = 0,null,([Measures].[Behavioural
Score] / [Measures].[Number of Accounts]))
2) IIF([Measures].[Score Id] = 0,NULL,[Measures].[Behavioural
Score]/[Measures].[Number of Accounts])
3) ([Measures].[Behavioural Score]) / IIF(isempty([Measures].[Behavioural
Score]),0,1)
4) AVG({[Measures].[Behavioural Score]})
5) [Measures].[Behavioural Score] / [Measures].[Number of Accounts]

What am I doing wrong?

If you're using AS 2000, the simplest solution will be to filter out
fact records where Score values are NULL. But if you need those record
for other measures, you can create another cube for those measures, and
combine the 2 cubes in a virtual cube.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***