Question
Evgeny Shvarov · Jan 24, 2017

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?
00
1 0 5 363
Log in or sign up to continue

Replies

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? 

 

 

 

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!