New post
Question
· Jul 11, 2016

How to calculate the percentage of higher level

#MDX #InterSystems IRIS BI (DeepSee)

 

I'm trying to calculate the percentage of the top level with the SUM function.
But I don't understand the PARENT behavior.
It does not work in any way!

SUM({FilialD.H1.Categoria.CURRENTMEMBER.PARENT.ALLMEMBERS},MEASURES.[Valor])

SUM({FilialD.H1.Categoria.CURRENTMEMBER.PARENT.%ALL},MEASURES.[Valor])

Someone could explain me the difference between %ALL and ALLMEMBERS?

Discussion (2)0
Log in or sign up to continue
Alexander Koblov · Jul 12, 2016

Documentation explains both functions well and with examples, so I encourage you to look into them. Especially first two examples for %ALL function

ALLMEMBERS -- function that returns a set of all members of the given level or hierarchy
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

%ALL -- function that enables you to use a member while ignoring any ROW and COLUMN context that uses the hierarchy to which this member belongs.
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

As to your question about calculating percentage of the top level, here is sample that for each product calculates revenue percentage of total from all products (2nd column) and from category for this product (3rd column).

Sample pivot

RevenuePctOfAllProducts and RevenuePctOfParent are calculated measures that defined as follows:

RevenuePctOfAllProducts:

Measures.[Amount Sold] / SUM(Product.[All Product].%ALL, Measures.[Amount Sold])

RevenuePctOfParent:

Measures.[Amount Sold] / SUM(Product.CurrentMember.Parent, Measures.[Amount Sold])

So the full query looks like:

WITH
MEMBER [MEASURES].[RevenuePctOfAllProducts] AS 
    'Measures.[Amount Sold] / SUM(Product.[All Product].%ALL, Measures.[Amount Sold])' 
MEMBER [MEASURES].[RevenuePctOfParent] AS 
    'Measures.[Amount Sold] / SUM(Product.CurrentMember.Parent, Measures.[Amount Sold])'
SELECT NON EMPTY {[Measures].[Amount Sold],
                  [MEASURES].[REVENUEPCTOFALLPRODUCTS],
                  [MEASURES].[REVENUEPCTOFPARENT]} ON 0,
    NON EMPTY HEAD(NONEMPTYCROSSJOIN([Product].[P1].[Product Category].Members,[Product].[P1].[Product Name].Members),2000) ON 1 
FROM [HOLEFOODS]
1 0
Cheng Cheng · Jul 13, 2016

I think allmembers should return members of a given level.  For the parent.allmembers, because only one parent, so allmembers cannot give out the result as you expect. If you try parent.children, it will work :-).  like follow example. Anyway, just parent self also works.

 

with member measures.total As 'sum([product].p1.[product name].currentmember.parent.children, measures.[amount sold])' member measures.parentname As '[product].p1.[product name].currentmember.parent.properties("NAME")' member measures.[pct] As 'measures.[amount sold]/measures.[total]', format_string='#.###;' select product.[product name].members on 1, {measures.[amount sold],measures.parentname, measures.[total],measures.[pct]} on 0 from holefoods
 
                                     Revenue     parentname          total            pct
 1 Bagels (dozen)    $25,534.37          Snack   3,111,815.51          0.008
 2 Bundt Cake      $1,284,895.19         Snack   3,111,815.51          0.413
 3 Calamari (frozen$2,655,134.41       Seafood   2,655,134.41          1.000
 4 Cheerios (box)   $513,770.68         Cereal     513,770.68          1.000
 5 Donuts (dozen)   $230,228.68          Snack   3,111,815.51          0.074
 6 Free-range Donut $767,295.26          Snack   3,111,815.51          0.247
 7 Fruit Loops (box $513,581.19          Fruit     744,168.60          0.690
 8 Lifesavers (roll $149,187.22          Candy     149,187.22          1.000
 9 Onion ring       $577,490.13      Vegetable     884,450.97          0.653
10 Onion ring       $306,960.84      Vegetable     884,450.97          0.347
11 Penne (box)      $151,839.08          Pasta   1,940,939.62          0.078
12 Pineapple Rings  $230,587.41          Fruit     744,168.60          0.310
13 Pretzels (bag)   $460,421.74          Snack   3,111,815.51          0.148
14 Swiss Cheese (sl $518,826.37          Dairy     518,826.37          1.000
15 Tortellini (froz $904,496.54          Pasta   1,940,939.62          0.466
16 Unsalted Pretzel $343,440.27          Snack   3,111,815.51          0.110
17 Ziti (box)       $884,604.00          Pasta   1,940,939.62          0.456
---------------------------------------------------------------------------
Elapsed time:       .470672s

0 0