Calculated measure/dimension

Question

I'm relatively new to MDX and am trying to accomplish what I think should be an easy thing, but I haven't found any solution.

I have a sales cube and one of the measure is profit which can be negative or positive. I want to get one measure which is effectively the sum of positive profit, i.e. only include in the new measure those profit numbers that have a positive profit.

The trick here is this is on the row detail level and something like

WITH MEMBER Measures.PositivePNL as IIF (Measures.PNL > 0, Measures.PNL,0)

doesn't work as that only works with the aggregate number

Answer 1

If you want to do something in MDX on the row detail level you will need a dimension containing the ID of the fact table (so each member in the dimension represents a row in the fact table). Then you can write a calculation:

WITH MEMBER Measures.PositivePNL as
'
Sum([DimFactId].[DimFactId].Members, IIF(Measures.PNL > 0, Measures.PNL, 0))
'

But this can be slow if you have a lot of rows in your fact table. Alternative is to add a column in your fact table containing only the positive values of PNL.

Answer 2

I should have mentioned that I am using Mondrian/MySQL. I came to similar conclusion but found a way to create a new degenerate dimension using an SQL key expression (so I don't actually need to add column to table):

<Dimension name="PNLCategory">
    <Hierarchy hasAll="true">
        <Level name="PNLCategory" column="pnlCategory" uniqueMembers="true">
            <KeyExpression>
                <SQL dialect="generic"> <![CDATA[IF(pnl >= 0,'Winner','Loser')]]></SQL>
            </KeyExpression>
        </Level>
    </Hierarchy>
</Dimension>

Now it becomes easy to do the calculated member:

<CalculatedMember name="WinnersCountByPNL" aggregator="count" dimension="Measures">
  <Formula>([PNLCategory].[Winner], Measures.PNL)</Formula>
  <CalculatedMemberProperty name="FORMAT_STRING" value="$#,###"/>
  <CalculatedMemberProperty name="DATATYPE" value="Numeric"/>
</CalculatedMember>     

so here I restrict the summation to only "winners" and the benefit is that Mondrian will not retrieve count(fact) entries from the row table.