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.
- How to remove/avoid committed files to go to master Branch from remote branch
- Git branch overwrites master when merging without offering conflicts
- Is Dropbox a valid quick-and-dirty source control solution?
- Is there a Windows version controll client that supports Git, Mercurial, and Subversion (all in one)
- Using Pagelime CMS with Version Control set up (git)
- Git: Manage each version of my app?
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.