Hi, Cheng! 

Thank you for the explanation! Great use case, btw. Now it's more clear with your initial post.

It would be great if you include Level_number in the query too to see what rows has different Level_number.

Like this:

WITH MEMBER measures.[with avg summary info] AS

                              'IIF(Product.CurrentMember.Properties("LEVEL_NUMBER") = 1,

                                 AVG(Product.[Product Category].Members, measures.[amount sold]),

                                 SUM(Product.[Product Category].Members, measures.[amount sold]))'

               MEMBER measures.[total count] AS 'SUM(Product.[Product Category].Members)'

SELECT {Product.[Product Category].Members, [Product].[All Product]} ON 1,

               {Product.CurrentMember.Properties("LEVEL_NUMBER"),measures.[with avg summary info],measures.[total count]} ON 0

FROM Holefoods

I suppose 'All Product' level should have the Sum of all Product levels. But in your example 'All product' lesser than 'Snack' for example.

ClassMethod PropertyGetStored(id)

For datatype properties this method returns their logical value, for object properties, it returns the id. It’s a wrapped global reference to the class data global and the fastest way to retrieve the singular property value. This method is only available for stored properties.

Eduard, you say it's the fastest way to take the value. Did you measure it?

Thank you John, some of your points here really make sense. We'll think about it.

You can be sure, that all posts in InterSystems Data Platform Blog  were reviewed by InterSystems engineers team and are promoted by InterSystems product managers.

Speaking about the high standard of technical and professional competence of engineers here in the community, I think votes, views and comments for the posts and answers can indicate the competence. I'm quite sure that there are a lot of engineers in InterSystems Developer Community (DC) who has high competence in InterSystems technology.

We have plans to introduce some signs/badges here on DC, which will indicate competence in different parts of InterSystems technology and products upon their contribution to Developer Community.