How to use an IF statement in a measure?

[Badrul]

I have this measure that I'm trying to get right:

FinalAmount:=IF(values([accountDisplayTxt])="Net Parent investment",1,[Amount000])

But I get the error:
The value for 'accountDisplayTxt' cannot be determined. Either 'accountDisplayTxt' doesn't exist, or there is no current row for a column named 'accountDisplayTxt'.

Any suggestions?

 

[Badrul]

Ok along the same path I'm now trying to get this to work:
=IF(HASONEVALUE(tblFSLineItem[FSLineItemDisplayTxt]),if (VALUES(tblFSLineItem[FSLineItemDisplayTxt])="Net Parent Investment",1,2))


Nothing seems to work when it comes to IF statements...grr...!

 

[MD610]

The first error was likely being caused by the 2 things you address in your second equation.

1. You generally need to include the table name when referencing a column. Even when it is not required, it is a best practice to do so.
2. When you use VALUES() in a boolean test, it can only return a single value. VALUES() returns a single column table of multiple values, so to use it in a boolean, you must first test to see if it is a single value with HASONEVALUE().

As far as I can see, without seeing sample data or knowing what your pivot configuation looks like, the second equation looks like it should probably be valid.

Is it giving you an error or just an unexpected result?

 

[Badrul]

Yeah second one did work just wasn't refreshing properly initially. However I'm kinda going round the houses trying to do what I want here so let me first explain clearly what I'm trying to do and see what people think is the best way of doing it.

I have a table with roll ups that looks like this

Total Assets 1000
Item 1 500
Item 2 500

Total Liability & Equity 800
Item 3 500
Balancing Item 300


So the total lines are automatic sub-totals that are calculated.

Ok -- I want to automatically adjust my balancing Item so that when it gets summed up with Item 3, the total Liabilitity & Equity Line always equals the total assets line (in this case 100, so Balancing Item needs to be increased to 500)


However I want this to be a solution that works whatever the numbers actually are. It always balances.

So far I've created a BalancingAmount measure that gives me the difference that I need to add on to "Balancing Item".

How do I now modify Balancing Item so that it is Balancing Item + BalancingAmount and that this modified number is rolled up into the sub-totals?

**Additional info: The fields I drop into PowerPivot to get the hierarchy is:
FSLineItem
AccountName

So "Total Assets" is an FS Line Item, and Item1 is an AccountName

 

[MD610]

I'm a little confused by some of the details. In a very general sense I can say that you can't use a measure inside itself.

For example Balancing Item = Balancing Item + Balancing Amount is not valid and will probably give you a circular reference error.

I would try to get around this by creating a second measure, with a different name, that is identical to your original Balancing Item.

So maybe something like this: Balancing Item = Balancing Item 2 + Balancing Amount

I think I would need to see some sample data to truly understand your problem.

 

[Badrul]

So to be more clear, I start with something that looks like this:
[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[/TR]
[TR]
[TD]Liabilities & Equity[/TD]
[TD]800[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD] Item 1[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD] BalancingItem[/TD]
[TD]300[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The field I have in the Values section of my Pivot is 'Amount'

I then create a measure called BalancingAmount which is 200 for Year 2010 and 100 for Year 2011 (I think I've cracked this part of the problem)

I now want to create Amount2. Amount2 is the same Amount for all items except for BalancingItem. For this Amount2 = Amount + BalancingAmount (which is the measure i created)

The end result would be:
[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[/TR]
[TR]
[TD]Liabilities & Equity[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]BalancingItem[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Happy to email you a sample file if you think that might help?

 

[Badrul]

I'll start another post as I'm now going off the topic of my original question.