UPDATED: Unexpected Results on "Aggregate within Set" calculation

[adam_mc]

I have the following crosstab over a dimensional source:

                                                             Last Month
                                             Sls               Top300    Top300 Total
(Product) Division Code                               Sls              Sls
                                              A                 B,C,D,E...       X                             

Product Division is the highest level in my product hierarchy
Last Month is a calculation as follows:           closingPeriod ([Cube].[Time].[Time].[Week])
Top300 is a calculation as follows:                 topCount([Item], 300, [Total Sls Amt]) where Item is the second level in my product hierarchy.
Top300 Total is a calculation as follows:        aggregate (currentMeasure within set [Top300])
Sls is my measure.

I am getting the correct totals in A, the correct totals in B,C,D,E for each of my Top300 items, but the totals in X do not represent the sum of the totals in B,C,D,E as I would expect.

I am also getting the following warning message when validating my report:
OP-ERR-0242 Nested calculations using the Aggregate function on sets in the same hierarchy may not return the expected results: 'dataItem="Top300 Sls"; expression="aggregate( currentMeasure within set [Top300] )"' , 'dataItem="Summary(Division Code)"; expression="member(aggregate( currentMeasure within set members([Division Code0@Division Code$]) ),'gen_mun_001','Summary',[Cube].[Product].[Product])"'.

Any thoughts on how to resolve would be greatly appreciated.
Thanks in advance,
Adam.

[MFGF]

I have the following crosstab over a dimensional source:

                                                             Last Month
                                             Sls               Top300    Top300 Total
(Product) Division Code                               Sls              Sls
                                              A                 B,C,D,E...       X                             

Product Division is the highest level in my product hierarchy
Last Month is a calculation as follows:           closingPeriod ([Cube].[Time].[Time].[Week])
Top300 is a calculation as follows:                 topCount([Item], 300, [Total Sls Amt]) where Item is the second level in my product hierarchy.
Top300 Total is a calculation as follows:        aggregate (currentMeasure within set [Top300])
Sls is my measure.

I am getting the correct totals in A, the correct totals in B,C,D,E for each of my Top300 items, but the totals in X do not represent the sum of the totals in B,C,D,E as I would expect.

I am also getting the following warning message when validating my report:
OP-ERR-0242 Nested calculations using the Aggregate function on sets in the same hierarchy may not return the expected results: 'dataItem="Top300 Sls"; expression="aggregate( currentMeasure within set [Top300] )"' , 'dataItem="Summary(Division Code)"; expression="member(aggregate( currentMeasure within set members([Division Code0@Division Code$]) ),'gen_mun_001','Summary',[Cube].[Product].[Product])"'.

Any thoughts on how to resolve would be greatly appreciated.
Thanks in advance,
Adam.

It's not clear from the formatting of your example in the thread whether Top300 is in columns or rows? If in columns, does it really make sense to have two levels from the same hierarchy in both rows and columns? Can you explain?

MF.

[adam_mc]

MFGF...

The only row is Division Code.

Sls, Top300, and Top300- Total are all under Last Month, and then Sls is repeated under Top300 and Top300 Total.
My plan is to remove the Top300 column and underneath sales (that list out all the items within Top300), so I would just end up with Top300 Total (and measure Sls) as a column.
I would then have a similar construct for LY Last Month next to Last Month.

However, I understand from your comment that having the same hierarchy in both columns and rows is likely causing my problem (I had thought that might be as much from the error).

Can you suggest and alternative solution?

Thanks in advance,
Adam.

[adam_mc]

I took [Top300] and added it as a slicer, this now makes the totals correct for my Top 300 items.
However, I need also to be able to say how much of each Divisions total is made up of the Top 300 items, so a slicer is not the way to go!

What I think I need to do is create a calculation to filter my set of Total Sales by my Top300 Items and thus get at total for these item and have an "unfiltered" item that is my total Divisional sales.
As I said, I am not sure that I am 100% on the correct line, but I am having difficulty setting up the filter calculation syntax.

filter ([Division], <if Item is in [Top300]>)?

Any help would be gratefully appreciated.
Thanks in advance,
Adam,

[adam_mc]

I am still having problems and would appreciate any thoughts anyone has...

I now have my calculation:
[Top300] = aggregate([Total Sls Amt] within set Filter(TopCount([Item] , XXX , [Total Sls Amt]) ,  tuple( currentMember(hierarchy ([Division Code])), [Total Sls Amt] ) > 0 ))

where XXX is the number of Top items I want (in this case 300).

However, whatever I set it to I always get the Top XXX items for each Division Code, rather than the breakout by Division of the Top XXX items regardless of Division.

My crosstab looks as follows:


                              [TY Week]
                              [Top 300]
[Division Code]         99999


[Division Code] and [Item] are in separate Dimensions.

Thanks in advance,
Adam.

[adam_mc]

I have found a workaround by adding various Top300 measures to the cube build itself.

However, as this is a cube build solution, I have now somewhat "hard-coded" the build to only All Measures and Top300 Measures.
If the business changes its mind tomorrow and wants Top500 instead of Top300, I will have to change in the cube - Not an ideal solution!

If anyone has any thoughts to a reporting solution, I would be extremely grateful.

Thanks in advance,
Adam.