Hi,

Can any one tell me, why the Grand total is showing 56.39 even when i dont have value for each row?

Hi 

There are a few reasons this could happen in analysis services.

Someone could have used the SCOPE statement, this allows you to have different calculations at different levels.

it is also possible that a calculation like this was used to round off small values

WITH MEMBER MEASURES.IIFDEMO AS
	IIF([Measures].[MyMeasure]< 1, NULL,[Measures].[MyMeasure] )

Someone may have used this approach believing that it would round off the value, however the [ALL] member which is evaluated separately would sum to a value greater than 1 therefor display.

Hi,

What i have identified is, it is calculating total(56.39) of all values and showing in the grand total, even after i apply the filter. Don know why it is not considering the filter applied on the first column while aggregating.

Hi,

Can u please tell me how to use SCOPE on the calculated member?or i need to write on Script command?

I just wrote like this below on calculated member , but shows syntax error for measure(second column). 

SCOPE
    (
          [Dim Items].[Item].[Item].MEMBERS,
          [Dim Fiscal Calendar].[Month].[Month].MEMBERS,
          [Dim Channel Hierarchies].[Channel Key].[Channel Key].MEMBERS,
          [Dim Product Hierarchies].[ABC].MEMBERS
    );
    THIS = [Measures].[Cost_Sum]/ [Measures].[Cost_Count];
End Scope;

Hi,

I wrote like this,

CALCULATE;    
CREATE MEMBER CURRENTCUBE.[Measures].AVG_Val as NULL;

SCOPE([Dim Items].[Item].[Item].MEMBERS);
    [Measures].AVG_Val =
        [Measures].[Val_Sum]/[Measures].[val_Count];
END SCOPE; 

But now, all Avg_Val is blank.

I do not think the scope statement is you problem, But it would be good for you to check, Can you confirm if you are using a statement similar to

WITH MEMBER MEASURES.[AVG_Val] AS
	IIF(([Measures].[Val_Sum]/[Measures].[val_Count]) < 1, NULL,[Measures].[Val_Sum]/[Measures].[val_Count] )

to hide small values.

The reason I ask is because when analysis services calculates the "Grand Total" or the [ALL] member it does not add up the values for [Measures].[Val_Sum] and [Measures].[val_Count] then performing the division, it is at this point that the value  56.393.. is being generated.

Would you be able to show the result of this query

select 
  ({[Measures].[Val_Sum],[Measures].[val_Count],[Measures].AVG_Val}) on 0
  ,([Dim Items].[Item].[Item].MEMBERS) on 1
from --insert Cube name here

as well as the current calculation you have for AVG_Val before you started adding the Scope statement. 

Another possibility is VisualTotals, if you are using dimensional security. If this is the problem, you can fix it by checking the "Enable Visual Totals" checkbox in dimension security.