Calculated/derived measures

Hello again.

I'm wondering if it's possible to add some form of calculated field to my fact table.

Scenario... I have a fact table that records customer account balances a the end of each day. As this is a bank we're talking about, some of the balances are in credit & some overdrawn.

Since the AccountBalance field aggregates by default to sum, if for example I wish to view balances by product I get the NET balances (i.e. all debit & credit balances summed). This is OK, however obviously my people will want to be able to split these in to

Credit Balances

Product

a 100

b 100

c 100

Debit Balances

Product

a -50

b -50

c -50

as opposed to the current situation where I get

Balances

Product

a 50

b 50

c 50

Hope this is clear... question is... how do I go about it ? Calculated field, derived field... I tried a simple IIF calculation but the trouble is it went something like

IIF([measures].[FactAccountBals] >=0, [measures].[FactAccountBals] ,0)

to give me credit balances, however (as you can imagine) it returns ZERO for all debit balance accounts, whereas I want to ignore them... but how ?

You can make another column in your fact table that is either "Credit" or "Debit" and then make an attribute out of that.

Then you can add that attribute to your mdx statement and have items broken out by transaction type.

|||Sorry, stupid question, but how do I make an attribute out of a fact table field record ?|||

OK for anyone else here's what I ended up doing.

Created a New Dimension called Dim Baltype with just 2 records, Debit Bal & Credit Bal

Then ran an update query on the fact table where I added and extra field to signify Dr or Cr balance & linked that to Dim Baltype - This now enables me to split by Loans and deposits in the cube.

Thanks for the steer