InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:48:27 GMT20http://www.sqlservercentral.com/Forums/Topic970134-147-1.aspxI found a solution on SQLDev - [Not exactly what I was looking for but similar][url]http://www.sqldev.org/sql-server-analysis-services/aggregate-function-none-11910.shtml[/url]--------------------------------------------------------------------[quote]Hi Sandrine,I think you need to set the IsAggretable property for the parent-child hierarchy, not setting the aggregate function for the measure. Double click the Countries dimension, right-click Parent/Child hierarchy in the Attributes pane of the Dimension Structure tab, and then click Properties. Scroll to the IsAggregatable property, and then click False in the property’s drop-down list. After that, the subtotal/total will not appear.Aggregate function for a measure determines how to display the value along with the dimensions, it will change the fact value aggregate behavior, has no relationship with subtotal/total of a dimension. And if you select None for the aggregation, no aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure. If there is no value, it will show NULL. More information, see:http://msdn.microsoft.com/en-us/library/ms175623.aspx#AggFunctionHope this helps.Raymond[/quote]Tue, 17 Aug 2010 04:39:49 GMTzabistianhttp://www.sqlservercentral.com/Forums/Topic970134-147-1.aspxSteve,Thanks for your reply. No aggregated value but actual value of [PercentageAchieved]. a matrix like....-------| 2008 | 2009 | 2010-----------------------------John | 89.3 | 90.2 | 78.9Beck | 89.3 | 90.2 | 78.9Smith | 99.3 | 50.2 | 98.9Please let me know if it make sense Thank againZTue, 17 Aug 2010 00:21:48 GMTzabistianhttp://www.sqlservercentral.com/Forums/Topic970134-147-1.aspxZabastian,What exactly do you expect to see at the aggregated/higher/highest level of the cube? That is, if you don't want to sum (which I agree would be wrong) or Average the Percent Achieved value, when you are *not* filtered to a single student for a single class, what value do you believe should be shown? Mon, 16 Aug 2010 23:22:30 GMTstevefromOZhttp://www.sqlservercentral.com/Forums/Topic970134-147-1.aspxI have two dimensions and one fact.DimClass - ClassID - ClassName - ClassYear (YYYY)DimStudent - StudentID - StudentNameFactResult - ResultID - ClassID - StudentID - PercentageAchievedI don’t want to sum [PercentageAchieved]. As I understand I should set its [AggregateFunction] property to [b][None][/b] to avoid summing up of values. If I set to [None] it return blank but it's fine with [Sum]. I did try to set the [FormatString] property to [#,#.##] and [Percentage].Please note the value for [PercentageAchieved] is pre-calculated so I can't write calculated measure in this case...am I right? :unsure:Thanks in advanceZMon, 16 Aug 2010 22:32:21 GMTzabistian