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
2 Solutions collect form web for “Calculated measure/dimension”
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.
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.