Generic percent of grand total MDX expression returns wrong value with filter

0
votes

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 is 125 for all rows:

SUM(([Measures].[Montant], Axis(1)(0)))

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.

Can someone please help me figure out what's wrong with my MDX expression coz i've been stuck at it for too long and i don't seem to find a solution.

screenshot of 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.

1
ssasmdx
Can you add the MDX you have and a screenshot if possible? – SouravA
I updated my post with a screenshot and more details, i hope it's clearer now ... – Soukaina
Is denominateur a calculated member/measure? – SouravA
It's the denominator of the calculated member "test SOB", i put it in a different calculated member to see if it returns the right value of the grand total, that's all. – Soukaina
Your code looks ok and your concern seems to be why the value of denominator changes. I have never worked with SSAS 2005, so I cant comment on how or why the engine is behaving in this case. With a higher version where you don't have an option to drill down once the axis members are laid out, I don't think you will be facing this problem. Good luck, maybe some experienced folks can help you. – SouravA

1 Answers

0
votes

I'm wondering if the use of EXISTING will enforce the filter context in your denominteur calculation?

SUM(
  [Measures].[Montant], 
  EXISTING Axis(1).ITEM(0).ITEM(0).HIERARCHY.MEMBERS 
)