How to calculate the percentage of higher level
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.
SUM({FilialD.H1.Categoria.
Someone could explain me the difference between %ALL and ALLMEMBERS?
Comments
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=D2RMDX_AllMembers
%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=D2RMDX_percentAll
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).
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]
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