Post reply

SSAS SUM DISTINCT in MDX

  • April 1, 2010 at 8:48 am

    #225731

    hi

    anyone got a formula to hand for a SUM DISTINCT in MDX?

    if i have:-

    trade nominal

    abc 100

    abc 100

    abc 100

    cde 150

    fgh 200

    i'm aiming for 100+150+200 = 450

    seem to be always getting 650/5=130 or 650/3=216.66

    any ideas are welcome

  • April 8, 2010 at 2:18 am

    #1148080

    ok, got this working, although not brilliantly

    firstly: whilst there is no DistinctSum, there is a DistinctCount, use this and get it working with the thing you want to DistinctSum to get a

    [measures].[DistinctDeals]

    then join your main table to a query that has a primary key of the thing you want to sum (in my case BackOfficeID) and link into it, create a measure on the amount you want

    [Measures].[Volume Boid]

    this will be far too big, but dividing it (at least a the leaf level) is nearly there

    [Measures].[VolumeBOIDIntermediate]=[Measures].[Volume Boid]/[measures].[DistinctDeals]

    then you just need to tell your measure to always sum things up at leaf level

    sum

    (

    descendants

    (

    [Deals BOID].[BackOfficeID].CurrentMember,

    [Deals BOID].[BackOfficeID].levels( 2 )

    )

    ,

    [Measures].[VolumeBOIDIntermediate]

    )

    now this is working..BUT annoyingly it works better when there is a current measure in place (oh i have a sort of NULL avoidance thing

    iif(isempty([measures].[DealComponents]),null,

    and it's slow with some dimensions, not sure why...

    got a bit of help from here

    http://blog.itmagination.pl/post/2009/03/24/Distinct-sum-measures-(using-MDX).aspx

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply