Calculated measure for variance that changes based on member

[dax]

Hi everyone

I am working with RS version 10.1 and am trying to solve a problem with a profit and loss report that I've created.

Some background: I have a crosstab report that has 3 members on the row section - "Revenue", "Direct Expenses", "Indirect Expenses" and an additional calculated member named "Profit" with the expression "[Revenue] -[Direct Expenses] -[Indirect Expenses]".  On the columns of the crosstab I have one measure named "CP AYTDLC" (actuals) and another named "CP BYTDLC-Final" (budget) and I have a calculated measure named "Variance" that is currently calculating as "[CP BYTDLC-Final]-[CP AYTDLC]".  So this simple example is showing a very simple profit and loss report with actual revenue and costs, budgeted revenue and costs and the difference.  It looks like this:

	CP AYTDLC	CP BYTDLC-Final	Variance
Revenue	5,834,173.15	5,610,058.52	-224,114.63
Direct Expenses	4,217,164.94	4,349,266.40	132,101.46
Indirect Expenses	564,606.16	564,606.16	0.00
Profit	1,052,402.05	696,185.96	-356,216.09

The problem is that I want the "Variance" to calculate differently depending on the member: for "Revenue" and "Profit" I need "[CP AYTDLC]-[CP BYTDLC-Final]" and for all other rows I need "[CP BYTDLC-Final]-[CP AYTDLC]".  So in other words, if my actual revenue is higher than my budget revenue then the Variance will be a positive (good!) number.  Similarly, if my actual cost is higher than my budget cost then the Variance will be a negative (bad) number.

For the life of me I cannot figure out how to refer to the member name on the report and create an expression that will let me do what I need.  I have a background in working with relational databases and am quite new to working with dimensional data models.  I could do this with relational data using something like CASE WHEN MEMBER_NAME IN ('Revenue','Profit') THEN ACTUAL-BUDGET ELSE BUDGET-ACTUAL END.

I've spent a good hour searching the forum here and several more trying to find an answer to this elsewhere on the internet and also in the two Cognos books I've bought so I would really appreciate if someone could point me in the right direction.  I'm hoping that I'm just missing something fundamental in my understanding...

Thanks  :)
Dax

[Greg]

We typically do this with a variance multiplier measure in our model but you can achieve the same result by defining a calculated measure in the report.

Create a calculated measure named [Variance Multiplier] as
case caption( [Account] )
  when 'Revenue' then -1
  when 'Direct Expenses' then 1
  when 'Indirect Expenses' then 1
  when 'Profit' then -1
  else 1
end

Then change your variance calculation to ( [CP BYTDLC-Final] - [CP AYTDLC] ) * [Variance Multiplier]

[pricter]

Greg I do think that "case when" is supported on olap.

Similar way to achieve that is

Firstly to create a set with the "Revenue", "Direct Expenses", "Indirect Expenses" and "Profit".
Create two data items one for its calculation.
Add these calculation as columns
Create a variable in order to hide the column you want based on the member of the set. 

[dax]

Hi

Thanks to you both for your replies.

To Greg:  in your example you have used case caption ([Account]) - what exactly is Account?  Is it the name of the dimension?  That is what I was trying to understand in my question. 

To pricter: I understand how to create the set, and I can create a data item for each calculation (one as [CP BYTDLC-Final] - [CP AYTDLC] and the other as [CP AYTDLC] - [CP BYTDLC-Final].  What I am not sure of is "Create a variable in order to hide the column you want based on the member of the set"

I have been playing around with this (for most of the day!) using ideas from your responses.  I created a data item named PAndL with the expression set([Revenue],[Direct Expenses],[Indirect Expenses],[Profit]).  I then modified the variance to the following:

if (caption([PAndL]) in ('Revenue','Profit')) then
([CP AYTDLC]-[CP BYTDLC-Final])
else
([CP BYTDLC-Final]-[CP AYTDLC])


This seems to work, but I don't understand exactly what the Set function is doing and if this is an acceptable way to do it.  Is this going to be efficient in terms of how the report will run?

Thanks
Dax

[pricter]

The set function creates a "group" of members.

Its one of the most common functions regarding the reporting based on olap and
you do not afraid to use it.

[Greg]

In my example, [Account] is the level of whatever hierarchy you're using from the account dimension.  This I assume is what you've got on the row edge of your crosstab.  If it's a custom set then use that instead.  Is that what [PAndL] is?  The case statement may not work with the OLAP provider so in this case substitute an if statement.  The expression you've got above looks like it should work fine.

[dax]

thanks to you both, I have it working now.

:)