This problem is driving me nuts!!

[Badrul]

I'm very painfully getting my head round Powerpivot. I have to say the DAX syntax is not as easy to learn as SQL or VBA. But getting to my point...

I start with something that looks like this:
[TABLE="class: cms_table, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[/TR]
[TR]
[TD]Total Assets[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD] Item 1[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Liabilities & Equity[/TD]
[TD]800[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD] Item 2[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD] NPI[/TD]
[TD]300[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Challenge:
The field I have in the Values section of my Pivot is 'Amount000'. I want to create an "FinalAmount" in which the NPI row is such that the overall Liabilities & Equity will always match the Total Assets. So in this case NPI needs to change to 500 in 2010 for the maths to work.

Additional info:
The row headers are "accountName" and "Level2". I have a hierarchy table that maps account names to Level2.


What I have tried:
I have tried creating a measure called "TotalAssets" and "TotalLiabilities"

=CALCULATE([Amount000],tblFSLineItem[Level2]="Total Assets",ALLEXCEPT(tblFSLineItem,tblFSLineItem[FSLineItemDisplayTxt],tblFSLineItem[Level1],tblFSLineItem[Level2]))


and

=CALCULATE([Amount000],tblFSLineItem[Level2]="Total Liabilities & Equity",ALLEXCEPT(tblFSLineItem,tblFSLineItem[FSLineItemDisplayTxt],tblFSLineItem[Level1],tblFSLineItem[Level2]))

This gives me the right amount at the overall total line but a smaller amount at the drill down row level -- however I want to see this overall total for the year being replicated for all rows or at least the NPI row.



Once I have NPI calculated, I want to create FinalAmount which would be 'Amount000' IF the row is not NPI or 'Amount000' + NPI if the row is amount.



If anyone can help me on this I would be very very very grateful!

 

[Badrul]

No response from anyone?

Ok I have simplified my problem down to one request:

=if(COUNTROWS(values(PowerPivotView[accountDisplayTxt]))=1,IF(VALUES(PowerPivotView[accountDisplayTxt])="999999 Net Parent Investment",[NPI (balancing)],0),[Amount000]+[NPI (balancing)])

I need to modify the end part of this formula so that the [Amount000]+[NPI (balancing)] only occurs for all levels of my hierachy that contains account 999999.

Here is my Powerpivot field structure:

[Level1]
[FSLineItem]
[AccountName]


Which looks like this in a report:
AssetsTotal
CategoryATotal
Account 1
Account 2

LiabilitiesTotal
CategoryBTotal
Account 3

EquityTotal
Category CTotal
Account 5
999999 Net Parent Investment


I want the [Amount000]+[NPI (balancing)] bit of my above formula to be used for calculating EquityTotal and CategoryCTotal; but for the other sub totals I simply wish to use [Amount000]