My cube contains dollar amounts of transactions with a measure [Amt].
The cube contains a dimension called [Transaction Type] that provides a
breakdown of how the transactions were processed.

I want to create a calculated measure that provides the sum of [Amt]
for all transaction types except a small set of them -- and I don't
want to list the transaction types I do want to include. But I will
here just for the purposes of being clear in my example. Suppose the
[Transaction Type] dimension includes members 1, 2, 3, 4, ...and so
on... , 25. I want the calculated member to return the sum of [Amt] for
all transactions that do not have Transaction type of 11, 12, and 13.

Also, I expect to hide the Transaction Type dimension in the cube
because I want to control which slices of this dimension to present. In
the end, I will create several calculated measures that provide a
variety of combinations of transaction type values.

Thanks for your help,
David

[Transaction Type].[Case1OfSum] =
Sum(Except({[Transaction Type].[1]:[Transaction
Type].[25]},{[11],[12],[13]))

or

[Measures].[Case1OfSum] =
Sum(Except({[Transaction Type].[1]:[Transaction
Type].[25]},{[11],[12],[13]), [Amt])

In cube editor, set the visible of the dimension, [Transaction Type], to
false, but you can still reference it in your MDX statement.

Ohjoo Kown

<davidb (AT) mercurydata (DOT) com> wrote

Ohjoo,

Thanks for answering. I'm still having problems, however. The second
expression looked perfect, but it did not do what I expected. It
returned a result greater than the [Amt] measure even though it was
suppose to filter the measure.

I tried an additional experiment. I removed the Except function and
just used the expression

Sum([Transaction Type].AllMembers, [Amt]).

I expected this expression to return the same as [Amt], but it provided
a substantially greater number also.

I've worked with MDX before (but not for awhile). I thought I
understood what was going on, but I now believe I'm missing something
fundemental. Can you provide insights?

-David

The calculated member(CM) will return the same values according to its own
definition whatever you slice a member from [Transaction Type] dimension.
But [Amt] depends on your slicing.

If you slice [All Transaction Type], the CM will return values smaller than
[Amt]. If you slice other member, the CM will still return the same results
but [Amt] will return smaller results than before and can be smaller than
CM.

Next, AllMembers return all members from the dimension literally. If the
dimension has all member([All Transaction Type]), it is also returned.

Ohjoo Kwon

<davidb (AT) mercurydata (DOT) com> wrote

Ohjoo,

Many thanks for your help. I got your suggestions to work. I don't know
why the expression in my second message was not working, but I've
accomplished my primary goal.

Thanks again!
David

Do you mean Sum([Transaction Type].AllMembers, [Amt])?

If [Transaction Type] dimension has several levels including all level, the
calculated results can be duplicated as many times as the number of levels,
because AllMembers return all members from the dimension.

Ohjoo Kwon


<davidb (AT) mercurydata (DOT) com> wrote

Yes. [Tranaaction Type] has an All level and a second level. The count
was twice the expected amount. So...the lesson learned is never use
AllMembers anywhere except the lowest level of a dimension.

Thanks!
David


Ohjoo Kwon wrote: