Sum of measures without value

[qvixote]

Hello!

I have a report made over a powercube, with a table like this:

ITEM1: 1
ITEM2: 1
ITEM3: 0
ITEM4:
ITEM 5: 1
ITEM 6: 1
ITEM 7: 1
ITEM 8:
ITEM 9:
ITEM 10: 0


Each item is a diferent measure in the cube, and the table is previously filtered by dimensions using value prompts. The measures always have values 1, 0 or no value (NULL from the database). I need to create 2 calculated measures, one of them must sum all the values and show the result, and the other must count how many of them have a value (0 or 1). So, in the example, the first calculated measure must show 5 and the second calculated measure must show 7.

I tried to make the fist one like this:
[ITEM1] + [ITEM2] + [ITEM3] + [ITEM4] + [ITEM5] + [ITEM6] + [ITEM7] + [ITEM8] + [ITEM9] + [ITEM10]

But it only works when all items have a value (0 or 1), but when there's an item without value, it shows nothing.

And I have no idea how to make the second one.

Any idea?

[sdf]

each data item has different measures?
or each item is a different data item?
is this a crosstab?

what are 1,0, and no value? isnt this the output of a certain measure?

[qvixote]

Each data item is a different measure. This is not a crosstab, they are shown in singletons.

But I've solved it!

For the first I've made a calculated measure like this:

Code Select Expand


IF ( [ITEM1] is not null ) THEN
    ( [ITEM1] )
ELSE
    ( 0 )


And for the second one I did:

Code Select Expand


IF ( [ITEM1] is not null ) THEN
    ( 1 )
ELSE
    ( 0 )


Thanks!

[HalfBloodPrince]

coalesce ([ITEM1],0) is better way.