Geek Answers Handbook

How to define a calculated measure in MDX based on a dimension attribute?

I would like to create a calculated measure that only summarizes a specific subset of the records in my fact table based on the dimension attribute.

Considering:

Measurement

  • date of
  • LedgerLineItem {Charge, Payment, Write-Off, Copay, Credit}

Measures

  • Ledger amount

Relationships
* LedgerLineItem is a degenerate size of FactLedger

If I break LedgerAmount by LedgerLineItem.Type, I can easily see how much is credited, paid, credited, etc., but when I do not break it into LedgerLineItem.Type, I can not easily add credit, etc. to the pivot table. I would like to create separate calculated measures that summarize only a certain type (or several types) of accounting facts.

An example of the desired result would be:

| Year  | Charged | Total Paid | Amount - Ledger |
| 2008  | $1000   | $600       | -$400           |
| 2009  | $2000   | $1500      | -$500           |
| Total | $3000   | $2100      | -$900           |

, , . , - ETL, ETL, . , MDX, - , , ETL MDX, .

, , . , . ( [amount-bookger], , .

CREATE MEMBER CURRENTCUBE.[Measures].[Received Payment]
AS CASE WHEN ([Ledger].[Type].currentMember = [Ledger].[Type].&[Credit]) 
OR ([Ledger].[Type].currentMember = [Ledger].[Type].&[Paid])
OR ([Ledger].[Type].currentMember = [Ledger].[Type].&[Held Money: Copay])
THEN [Measures].[Amount - ledger] 
ELSE 0
END 
, FORMAT_STRING = "Currency"
, VISIBLE = 1 
, ASSOCIATED_MEASURE_GROUP = 'Ledger'  ;

, . , , , , , , 0 . ..

CREATE MEMBER CURRENTCUBE.[Measures].[Received Payment]
AS sum({([Ledger].[Type].&[Credit]), ([Ledger].[Type].&[Paid])
, ([Ledger].[Type].&[Held Money: Copay])}
,  [Measures].[Amount - Ledger])
, FORMAT_STRING = "Currency"
, VISIBLE = 1 
, ASSOCIATED_MEASURE_GROUP = 'Ledger'  ;

, Ledger.Type ?

+5
3

:

CREATE MEMBER CURRENTCUBE.[Measures].[Received Payment]
AS sum(Existing({([Ledger].[Type].&[Credit]), ([Ledger].[Type].&[Paid])
, ([Ledger].[Type].&[Held Money: Copay])})
,  [Measures].[Amount - Ledger])
, FORMAT_STRING = "Currency"
, VISIBLE = 1 
, ASSOCIATED_MEASURE_GROUP = 'Ledger'  ;

.

+7

, .

Aggregate Sum, , Sum:

CREATE MEMBER CURRENTCUBE.[Measures].[Received Payment]
AS Aggregate(Existing({([Ledger].[Type].&[Credit]), ([Ledger].[Type].&[Paid])
, ([Ledger].[Type].&[Held Money: Copay])})
,  [Measures].[Amount - Ledger])
, FORMAT_STRING = "Currency"
, VISIBLE = 1 
, ASSOCIATED_MEASURE_GROUP = 'Ledger'  ;
+1

I assume that the Ledger size has a key in FactLedger, but the problem that it does not collapse with the first MDX member computed makes me believe that you can rethink the hierarchies for this.

Then a simple SUM will work based on your type of book. Does it make sense?

0
source

More articles:


All Articles