How to create a "Distinct Sum" Measure ?

[sonalb]

Hello,

I’m trying to create a second measure in my schema which is a “SUM DISTINCT” of a particular column. Allow me to explain:

Fact Table:

orgID      txnDate    vendorName txnAmount  vendorMax

A1         2008-08-30 Vendor_1   100,000    500

A2         2008-09-15 Vendor_1   24,000     500

A3         2009-01-30 Vendor_2   26,000     200

A2         2009-01-10 Vendor_1   57,000     500

I have defined three dimensions:

1.)     Organisation [based on orgID]

2.)     Time [based on txnDate]

3.)     Vendors [Degenerate dimension on vendorName]

I already have one measure to sum the txnAmount.

I need another measure that can give me the rolled-up sum of vendorMax for distinct vendorID / vendorName. For example, in the table above,

For [All Organizations] & [All Years], the measure should return 700.

For [All Organizations] & Year=2008, the measure should return 500.

For [All Organizations] & Year=2009, the measure should return 700.

I’ve not had any success using MeasureExpression, or CalculatedMember. I’m not too sure if I can use a Join in this case, since I want a measure and not a dimension.

Would appreciate any help/pointers you may have.

Thanks in advance.

Sonal.

 

[ManfredL]

Try something like ..

 

        <Measure name="rowCount" column="orgID" aggregator="count"></Measure>
        <Measure name="vendorCount" column="vendorName" aggregator="distinct count"></Measure>
        <Measure name="vendorMax" column="vendorMax" aggregator="sum"></Measure>
        <CalculatedMember name="vendorMaxDist" dimension="Measures"
             formula="[Measures].[vendorMax] * ([Measures].[vendorCount]/[Measures].[rowCount])">
        </CalculatedMember>

 

in your schema file

 

By this, the vendorMax-sum ist  "normalized" by the ratio of the number of  distinct vendors with the number of rows in one cell.

Greetings

 

Manfred

[sonalb]

Thanks Manfred. Some of the values are being reported incorrectly. Am checking against the data in the system ... will post an update here after that.

 

Thanks again.