.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Calculated measure doesn't slice properly

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

Hi there,

I have a revenue cube has MTD net revenue measure, and I created calculated measure, YTD net revenue:


[Date DIM].[Fiscal Date].[Fiscal Year],

[Date DIM].[Fiscal Date].CurrentMember


([Measure].[MTD net revenue])



[Measure].[MTD net revenue] can be sliced by Date, Region, Office, Department but not YTD net revenue.

Our fiscal year starts with Jul, for example, MTD net revenue 

Month    Region Office   Department   MTD net revenue

Jul 2010, GTA, Toronto, DepartmentA 1111.00

Jul 2010 GTA Toronto DepartmentB 2222.00

Aug 2010 GTA Toronto DepartmentA 3333.00

but no MTD net revenue for Department B

When slicing YTD net revenue, everything looks good in Region office level

Month    Region Office   YTD net revenue

Jul 2010 GTA    Toronto  3333.00

Aug 2010 GTA  Toronto  6666.00

But it's not correct in Department level:

Month    Region Office   Department  YTD net revenue

Jul 2010 GTA    Toronto DepartmentA 1111.00

Jul 2010 GTA    Toronto DepartmentB  2222.00

Aug 2010 GTA  Toronto DepartmentA&n

View Complete Post

More Related Resource Links

Why feature with event receiver doesn't activate properly after site provisioning?



I have defined feature with event receivers for my document library and for users List .

I try to activate my feature in onet.xml in section <WebFeatures>. After provisioning a new site from onet.xml  all work fine and my feature is shown as activated, but event receivers doesn't fire. It fires only after reactivating my feature (deactivate, then activate it again). But I need my feature works immediately after site provisioning without deactivating and activating it again. Who knows how to solve this problem?

part of ONET.xml



        <Feature ID="00BFEA71-4EA5-48D4-A4AD-7EA5C011ABE5"/>

        <!-- TeamCollab Feature -->

        <Feature ID="F41CC668-37E5-4743-B4A8-74D1DB3FD8A4"/>

        <!-- MobilityRedirect -->

        <!--My EventReceivers Feature-->

        <Feature ID ="41178843-A6A4-47c7-9EF9-B8A432E02B88"></Feature>





Measure Dependent Calculated Measure

Hi Was looking to create a calculated Measure Which needs to be dependent on another column of the FACT table. I need to have the Average of the particular Measure based on the fact that this particular 'set' has a common Related dimension's The example would give a better picture. I have a set of dimensions like STUDENT, TEACHER, COURSE etc as my dimensions and the grades, class standing based on marks for the subject, percentage of marks scored, Percentile, Marks Scored,StudentID and the related PK's etc are the measures on my fact table Here I want the Avg of the marks scored by the Students based on the grade say is  A or B or C  I was looking to create a calculated measure by using MDX which was a follows (NOT THE CORRECT SYNTAX JUST THE IDEA) SUM  (MARKS SCORED)/COUNT(DISTINCT StudentID) WHERE GRADE = A As this would be measured against a particular subject or a teacher or a Course. I was hoping if I could get help in putting this in a proper SYNATAX   Thanks in advance.  

Please help with converting calculated measure.

Hi, I have to dimensions: [Currency] with members CAD and USD and [Convet to] with members None, CAD, USD. The measures involved in calculation are [Amount Billed] and [Amount Received] The problem calculation is defined pretty simple: MEMBER CURRENTCUBE.[Measures].[AR] AS (abs([Measures].[Amount Billed] - [Measures].[Amount Received])>0.1,[Measures].[Amount Billed] - [Measures].[Amount Received] ,null); and worked perfect without currency conversion . I've read some info about currency conversion and designed this calculation to convert: scope (leaves([Time 2])); scope([Convert To].[Convert To].&[USD],[Currency].[Currency].&[CAD]); [Measures].[Amount Received] = ([Measures].[Amount Received],[Convert To].[Convert To].&[None])/validmeasure([Measures].[Cdrate]); [Measures].[Amount Billed] = ([Measures].[Amount Billed],[Convert To].[Convert To].&[None])/validmeasure([Measures].[Cdrate]); End Scope; scope([Convert To].[Convert To].&[CAD],[Currency].[Currency].&[USD]); [Measures].[Amount Received] = ([Measures].[Amount Received],[Convert To].[Convert To].&[None])*validmeasure([Measures].[Cdrate]); [Measures].[Amount Billed] = ([Measures].[Amount Billed],[Convert To].[Convert To].&[None])*validmeasure([Measures].[Cdrate]); End Scope; End Scope; That calculation provides correct results for both [Amount Billed] and [Amount

Optimize Calculated Measure containing COUNT EXISTING

Hi, My goal is to change the text color of all cells that contain aggregated values. Currently I achieve it like this: I COUNT the members of all attributes of all dimensions. To be multi-select-safe I am using the EXISTING keyword. If the members count of at least one dimension is not 1 than the background color of the cell is changed. CREATE MEMBER CURRENTCUBE .[Measures].[SingleCellSelected]   AS iif ((COUNT (Existing ([Dim1].[Attr1].[ Attr1].MEMBERS ))=1) AND (COUNT (Existing ([Dim1].[Attr2].[Attr2].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr3].[Attr3].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr4].[Attr4].MEMBERS ))=1) AND (COUNT (Existing ([Dim3].[Attr5].[Attr5].MEMBERS ))=1),1,0),   VISIBLE = 0  ;    SCOPE ([Measures].AllMembers ); FORE_COLOR (this ) = iif ([Measures].[SingleCellSelected]=1,0,16744448); END SCOPE ; This approach works but it performs badly with attributes with many members. Do you have any idea how to optimize this? Thank you!

Calculated measure between two dates

Following fact table - Amount - StartDate - EndDate - SomeOtherDate   If the user selects a date in the time dimension I want to build the sum of Amount where the selected date is between StartDate and EndDate. Like this   SELECT SUM(Amount) WHERE <date> between StartDate and EndDate   Is it possible to create a calculated measure like this.   Thanks.

WSS 3.0 Knowledge Base Template doesn't function properly after SharePoint 2010 migration

I migrated our WSS 3.0 instance to SharePoint 2010 in a dev environment.  So far, everything works with the exception of the most heavily utilized portion of our WSS 3.0 installation – which is a "Knowledge Base" sub-site utilizing the following features:   Knowledge Base List Instances: List Instances for the Knowledge Base Application Template Knowledge Base Modules: Modules for the Knowledge Base Application Template Knowledge Base Provisioning Handler   Link to Knowledge Base Template http://www.microsoft.com/downloads/details.aspx?FamilyId=BC4E5C4E-FBBB-4666-8E32-C4CEE09E1FE6&displaylang=en   Two issues exist with the Knowledge Base after migrating.   1.       The “Write new Article” feature which uses a content type called “Wiki Knowledge Base Article” use to open a “New Wiki Page” that provided the all of the associated fields specified in the List Content type settings.  Upon creating the new Wiki Page (i.e. Knowledge base article) it would associate it with the “Knowledge Base” document library.  The “Write new Article” feature now fails to utilize / recognize the Content Type settings and instead create a blank New Page without any fields except for “New page name:” and also creates a “Site Pages&

Which version of Excel to install for a calculated measure?

I have a 64bit server running SSAS 2010 and I keep getting an ExcelMDX error when processing my cube. We do use an Excel function called FINV() in a calculated measure. I installed Excel 2007 on the SSAS 2010 server but this did not help (I also tried Excel 2010). I can start Excel on the server so I know it is installed. Does it matter whether I install Excel 32bit or 64bit on the server? How do I verify that Excel is installed correctly for SSAS 2010? Errors and Warnings from Response  MdxScript(Sales and Returns) (94, 8) The '[Excel].[FINV]' function does not exist.Kerry

Calculated Measure Help - Comparing Months from two Date Dimensions

Hello:   I have a Measure Group that has two Date dimensions, a Reporting Date and a Fee Paid Date. The Reporting Date and the Fee date is based on the same Dimension, which has a standard YQMD hierarchy. It's very typical. The calculation is called "Fees Paid In Same Period", and it would display the Fees paid if the Reporting Month equals the Fees Paid Month.   How do i do this?   Thanks,

Calculated Measure scenario with a filtered dimensional attribute

I have a cube with a Measure (A) and a dimension (C).  I've created a Calculated Measure (B) whose tuple is Measure A with the all member of Dimension C.  The Calculated Measure looks like this: ([Measures].[A], [C].[ALL]) Scenario 1 When I place the dimension on a pivot table, the Calculated Measure B reflects the correct value, Measure A by all members of dimension C. Scenario 2 When I place the dimension in the filter area and filter by one or more members, the Calculated Measure B reflects the correct value, Measure A by all members of dimension C.  The same answer as Scenario 1. Scenario 3 When I place the dimension in the pivot table AND filter by one or more members, I am getting a different answer than I want.  It is filtering first by the members selected and then reflecting the Calculated Measure as the All value of ONLY the selected filter members.  I want the calculated Measure to reflect Measure A by all members of dimension C.  (The same number as in the first two scenarios)   Is there any way to create the Calculated Measure differently so that all three scenarios give me the same answer?  

Errors with calculated columns, the column posts, but doesn't calculate.



I have a custom list that isn't processing calculated columns anymore.

When I post a calculated column I get one of two error messages...either "SQL Server may not be started" or "The value does not fall within the expected range".  I've noticed the sql message before but the changes are made and calculations in the past have worked ok.  Now that the "value" error is cropping up it's quit calculating the columns.

i.e. I open a calculated column, simply have it reference a numeric field (=[MyNumberField]).  When I hit ok I get one of the two error messages.  When I look at the value of the calculated column it's blank.

Any references to articles, etc. would be helpful.  There are 406 columns in this list...

Best Regards,


cummulative calculated measure


HI Guys

i have time Dimemsion and hiriarchy as Year-month-week-day

i have a account Dimension 

Actual as measure

i want to create a Calculated measure for Balance Account which is an member in account dimension

i want to create a calculated Cumulative measure for balance sheet account like the below

which should give me sum of cumulative of actual amounts for Required period


Time                           FY2010-Period1      FY2010-Period2      FY2010-Period3

Balance Sheet                  $2000                      2100                  4100  

the above will calculate the Balance of previous month + current month so on

i hope i am making sense

Kind Regards



Please Help with Defining Calculated Measure based on Dimension Members



The issue looks pretty simple yet I got stuck. I want to define the measure [NR Var] as [NR]-[F NR] for years/months/dates before 2009 and [NR]-[FI NR] for 2009 and on. I am using [Year]-[Month]-[Date] hierarchies in the cube. I have defined scope:

Scope ([Measures].[NR Var], {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2006], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2007], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2008], 2, self_before_after)});
this=[NR]-[F NR];
End Scope;

but it does not work for some reason. If I get rid of DESCENDANTS function, it works but applies the scope only to the YEAR level. Another problem with using SCOPE is that it affects [NR Var] only yet I have other calcs derivative of [NR Var] which I want the scope to affect as well.

So I ideally I would like to have something like:



case when {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_befor

Calculated measure that works across multiple hierarchies

Dear all,

I need to create a calculated measure for YTD that dynamically calculates based on the date hierarchy selected by the user (SSRS & Excel).

On the date dimension, I have 2 hierarchies "Year-Month-Day" and "Year-Week-Day", that both share the year and day attributes.

What's the best way to go about this?

Many thanks,


Windows Authentication Doesn't work properly



I've implemented Windows Authentication with kerberos protocol.

Each time user visit site they have to hit Sign in button and then they get logged in. IE doesn't recognized automatically user and signed in.

Thanks, Sam

Creating a calculated measure that filters



Can I create a calculated member that will filter out based on a certain dimension? eg, I have a customer dimension and a sales cube. The sales cube has the sales count measure. Can I have a calculated measure that will give me the sales count for all customers that are flagged as 'New'?  That way, I have a total count and a New customer count.



Problem with a calculated measure that works across different time hierarchies.


I was inspired to create a calculated measure which works with all available time hierarchies after reading this original post.


I created   a calculated measure called ‘Selected Hierarchy’ as follow which returns either ‘Admit’, ‘Received’ or ‘Discharge’ as the hierarchy is selected.


when NOT IsError(Extract ( Axis (0), [Admit Date].[Date Hierarchy]).Count ) then 'Admit'

mdx calculated measure sum of sick duration for all sick cases ended in period T



I'm using SQL server 2008 standard edition.

I have a cube with measure groep sickness, and dimensions date and employee.

In the sickness fact table i have employeeID, sick ID, sick start date, sick end date. The employeeID is linked to employee dimension, and both the sick start date and end date are linked to the date dimension.

Now i want to create a calculated measure, which calculates the sum of the duration of all sick cases that ended in period t.

So that I later can create a report with on one axis the period (months) and on the other axes the sum of the duration of all sick cases ended in that month.

I already have a measure which counts sick duration. But how can I add the condition that it only takes the sick cases ended in a period?

Regards, Hennie



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend