Generic Percent of grand total MDX expression

  • mrani.soukaina

    Right there with Babe

    Points: 741

    Hello everyone,

    I have this problem to find a generic MDX expression that returns the percent of grand total regardless of the dimension that i drag in the SSAS cube browser.

    Now i'm using this expression :

    ([Measures].[Montant], Axis(1)(0)(Axis(1)(0).Count - 1).dimension.currentmember)/SUM(([Measures].[Montant], Axis(1)(0)))

    it works fine, but when i filter on the inner item of the axis, the expression returns a wrong value

    For example :

    i have in my rows axis 3 items : Year > Brand > Category

    The grand total : SUM(([Measures].[Montant], Axis(1)(0))) is 125 for all rows

    If i filter on the categories , the grand total changes, lets say it is equal to 65 now for the outer items of the axis. But when i drill down to see its value for the categories, i find it still equal to 125. and as a result the value of percent is wrong as well.

    here attached a screenshot of the cube browser.

    The calculated measure is "test SOB", MDX expression : ([Measures].[Montant], Axis(1)(0)(Axis(1)(0).Count - 1).dimension.currentmember)/SUM(([Measures].[Montant], Axis(1)(0)))

    the grand total is "denominateur", MDX expression :SUM(([Measures].[Montant], Axis(1)(0)))

    as you can see, the value after filtering with Onglet = "DIGITAL" is 182.50 but when i drill down the brand "Beauty" to see "denominateur" per category, i find the value 338.05 which is the value of "denominateur" before applying the filter.

    I hope it's clear enough...

    Thanks.

  • hlo11

    Newbie

    Points: 5

    Hey did anyone ever answer your question or did you figure out why filtered values were not being calculated correctly?

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

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