How to calculate percentage of all for a member in DeepSee?
Hi, All!
What is the calculated measure expression for the percentage of a member?
Say in Holefoods I want to see in a Cols Online sales in Revenue, All the revenue and % of Online sales for the Revenue?
I have the following:
With expression:
SELECT NON EMPTY {NONEMPTYCROSSJOIN([Channel].[H1].[Channel Name].&[2],[Measures].[Amount Sold]),[Measures].[Amount Sold]} ON 0 FROM [HOLEFOODS]
How can I add the percentage of All in Revenue for Online channel?
Evgeny,
for these purposes I use a calculated measure. The MDX-expression for the measure looks like this:
[Measures].[Amount Sold]/
%MDX("select [Measures].[Amount Sold] on 1 from holefoods")
My complete MDX is:
SELECT {[Measures].[Amount Sold],[MEASURES].[Percentage]} ON 0,NON EMPTY [Channel].[H1].[Channel Name].Members ON 1 FROM [HOLEFOODS]
Hope this helps.
-Michael
Michael thanks! It works for nonsliced data. But if I include dimensions on the rows it shows percentages which doesn't include Online dimension:
The MDX expression:
SELECT NON EMPTY {NONEMPTYCROSSJOIN([Channel].[H1].[Channel Name].&[2],{[Measures].[Amount Sold],%LABEL([MEASURES].[PCTMICHAEL],"","#%")}),[Measures].[Amount Sold]} ON 0,NON EMPTY [Outlet].[H1].[Region].Members ON 1 FROM [HOLEFOODS]I mean, what should I do if I want to see 100% for Pct in Total row?
Sample screenshot
Hi Evgeny,
you can add a context parameter to the MDX exression. It an be filters, rows or columns. So if I change my code to:
[Measures].[Amount Sold]/
%MDX("select [Measures].[Amount Sold] on 1 from holefoods","%CONTEXT","columns")
it gives me the follwoing result:
Does this help?
Michael
Hi, Michael! That's it! Thanks!