How to calculate percentage of all for a member in DeepSee?

Question

Question

Evgeny Shvarov · Jan 24, 2017

#Analytics #Code Snippet #MDX #InterSystems IRIS BI (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?

Discussion (5)0

Log in or sign up to continue

Michael Braam · Jan 25, 2017

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

1 0

score 2 · Answer 1 · 2017-01-24T05:47:02-05:00

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

score 0 · Answer 2 · 2017-01-24T06:08:00-05:00

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?

score 0 · Answer 3 · 2017-01-24T05:58:10-05:00

Michael Braam · Jan 24, 2017

Sample screenshot

0 0

score 0 · Answer 4 · 2017-01-25T04:43:13-05:00

Evgeny Shvarov · Jan 25, 2017

Hi, Michael! That's it! Thanks!

0 0