In OLAP cube wrong Grand Total when attribute is filtered

Question

A user trying to check the Sales Amount per Salesperson. Sample data:

Salesperson   Sales Amount    
001                   1000    
002                    500    
003                    750
Grand Total:          2250

It looks fine, but we have the following hierarchy Company > Class > Group > Subgroup in the cube and if a user tries to use this hierarchy in filters - Grand Total fails (if any attribute is unchecked in this hierarchy). Sample:

Salesperson   Sales Amount    
001                   1000    
002                    500    
003                    750    
Grand Total:           350

I've noticed the same problem before when we tried to filter Date attribute, if not every day of the month was selected it shown wrong Grand Total too.

Have you an idea why it happens and how to fix it?

Sales Amount is physical measure (not calculated measure), it is selected from SQL view (the same happens with every fact).

I've asked the same question here, but nobody could answer it.

I've tried to delete all MDX calculations (scopes), but still Grand Total was incorrect.

EDIT

I've noticed that the problem occurs when filtering like that:

1 element selected from the first level of the hierarchy, 1 element from 2nd level and 1 element from the 3rd level of hierarchy as in the image above.

If the 3rd level isn't filtered it shows good Grand Total.

EDIT 2

I've tried to trace on SSAS, it returns exactly the same output as in Excel. It generated the following MDX when using Salesperson dimension on the rows:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, 
NON EMPTY { ([Salesperson].[Salesperson].[Salesperson].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, 
MEMBER_UNIQUE_NAME ON ROWS FROM ( 
SELECT ( {  [Item].[Class - Group - Subgroup].[Class].&[XXX]&[1.], 
            [Item].[Class - Group - Subgroup].[Group].&[XXX]&[2.]&[2.2.], 
            [Item].[Class - Group - Subgroup].[Subgroup].&[XXX]&[2.]&[2.3.]&[2.3.1.] } 
) ON COLUMNS FROM ( SELECT ( { [Company].[Company].&[XXX] } ) ON COLUMNS 
FROM [Sales])) 
WHERE ( [Company].[Company].&[XXX], [Item].[Class - Group - Subgroup].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

This MDX generated without Salesperson dimension:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS 
FROM ( SELECT ( { [Item].[Class - Group - Subgroup].[Class].&[XXX]&[1.], 
[Item].[Class - Group - Subgroup].[Group].&[XXX]&[2.]&[2.2.], 
[Item].[Class - Group - Subgroup].[Subgroup].&[XXX]&[2.]&[2.3.]&[2.3.1.] } ) ON COLUMNS 
FROM ( SELECT ( { [Company].[Company].&[XXX] } ) ON COLUMNS 
FROM [Sales])) WHERE ( [Company].[Company].&[XXX], [Item].[Class - Group - Subgroup].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I've noticed even if I'm not using any dimension on the rows (in samples above I've used Salesperson dimension) it shows wrong Grand Total.

For example it shows:

Sales Amount 
350

And when using Salesperson dimension on the rows:

Salesperson   Sales Amount    
001                   1000    
002                    500    
003                    750    
Grand Total:           350

Have you tried debugging the difference by comparing the details of the 'Grand Total' (.ShowDetail) with the Rows. That should give an idea of where the difference is, incresing your chances of identifying the cause of the problem. — EEM
@EEM thank you for the suggestion, but I can't debug like that. ShowDetails can be used when only 1 attribute is selected in the filters list. Check the following error: i.ibb.co/QjRMgrT/Error.png When only 1 element is selected in filters it shows correct Grand Totals. It fails when I check a few (but not all) elements from the hierarchy (2-3rd level), sample: i.ibb.co/vDNCRZq/Sample-Of-Selection.png So this debug not working to solve this problem. — Infinity
Could you start trace on SSAS, capture actual MDX executed and run this MDX using Management studio? — Piotr
I don't know. I have Visual Studio (previously MSDN) subscription, which includes some tickets and then we have enterprise agreement. Check if anyone has it in your organization. docs.microsoft.com/en-us/visualstudio/subscriptions/… — Piotr

Mike Honey Mike Honey · Accepted Answer · 2019-04-27T07:55:47

I suspect you have an unusual Aggregate Function set on Sales Amount, e.g. ByAccount, AverageOfChildren. It should probably use Sum. Check the properties of the Sales Amount measure.

https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/use-aggregate-functions?view=sql-server-2017

In OLAP cube wrong Grand Total when attribute is filtered

2 Answers