.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

Slicing a Calculated Measure by the Dimensions Used to Create It

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
I have a calculated measure defined like this:
CREATE MEMBER CURRENTCUBE.[Measures].[Reduced Count]
 AS sum(
When I run the MDX:
  [Measures].[Reduced Count] on 0,
    [Date].[Date Year].&[2011],
  ) on 1
from WH
I get the same number for all actions. That number is the correct total for the whole year. What do I need to do so that I can break down the reduced count and see how many for each of the valid actions? I am sure I am missing something obvious.

View Complete Post

More Related Resource Links

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,

how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

Create a banding dimension that groups by a calculated measure


I want to show a count of orders banded into different amount ranges., ie <1k, 1k-5k,6-10k, etc.  The cube  (SSAS 2008 r2 enterprise) has a reporting currency dimension so I need to do the banding dynamically as each order amount is converted into the selected currency before being grouped into the appropriate band.   I also want to filter by other dimensions such as product or region.  Given this, it is not viable to do the banding calculation in the underlying data source and I assume a calculated measure is most appropriate.

Your help on what this would look like would be greatly appreciated.


Aggregations based on Dimensions dragged for Calculated measure from different fact table Using MDX



 I am a newbie to Mdx, i am working on education domain. I have a requirement to calculate student counts based on different fact views.

Especially for state,district and school levels we created three different fact views based on applying some conditions on original fact table.

Now i am using only one calculated measure named "Student Count" in my cube. My requirement is, when i drag my district dimension student count count based on district dimension should appear in my measure, if drag both districts and schools together then school facts student count should appear in my count, if i didn't drag any of the two dimensions then state level count defaultly aggregated.

For this, i thought write an mdx basing on dimension i am dragging, so i started with scope function for getting a different fact view based on different fact view,

Scope([Districts].[Districts],[Mesures].[Student Count])

this= ([Districts].[Districts],[Mesures].[District Student Count])

end scope;

like wise for school dimension and so on, but i am getting same value for both of the cases.

could any one help me out in getting solution for this. Is there anyother way other than creating new cubes for each level.

Thanks in advance. Any suggestions are appreciated.


Lakshman A N, S

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!

How to create a Measure without aggregations

Hi all, The data in the fact table has columns which are precalculated ranks for the products based on sales. How to create them as measures in the cube without any aggregations. I selected the properties for measure as "No Aggregations" while creating. But I dont see any data. Then I tried to create those columns as seperate dimensions by doing self join to the fact table.In this way I get the correct data,but the performance is slow. Is there any other way to try out which has both data and performance. Please help me out. Thanks, Sam

create a calculated member in a cube in SSAS

Hello   I’m trying to create a calculated member in a cube in SSAS and was hoping someone could help.   What I’m looking to do is count the number of occurrences of a value in a field.  I have Status field that contains New, Renewals and Enquiries and another field that contains a count, the problem I have and the reason for this query is the count is set to 0 for Enquiries.   The tSQL equivalent would be something like...   Select Date, Status , count(Status) From aTable Where Status = ‘ENQ’   And to summarise, what I’m looking to do would look something like the below   Date Status Count Commission Apr-10 NEW 1 100 May-10 NEW 1 100 May-10 REN 1 50 Apr-10 ENQ 0 0 May-10 ENQ 0 0 Jun-10 REN 1 50 May-10 NEW 1 100 Just looking at the ENQ by month. Date Count of ENQ     Apr-10 1     May-10 1     Jun-10 0       Any help gratefully received.

How do I create a calculated member for daily total revenue?

Hi everyone! I'm new to MDX and I can't figure out how to create a working calculated member. Here's business background: There are 2 measures in the cube - a and b. The formula for Total Revenue is a - b - $1000 ( don't ask me why =), 1000 is being subtracted from each day's Total Revenue - a ball park adjustment for something) My calculated member [Measures]. [Total Revenue] looks like this Case     When IsEmpty([Measures].[a]- [Measures].[b])     Then null     Else [Measures].[a]- [Measures].[b]-1000 Unfortunately this calculated member doesn't work correctly, it subtracts $1000 from everywhere and not from the intersection of [Dim Calendar].[HierarchyTime].[Day] and Total Revenue. How do I make it work in accordance with business logic? The objective is to adjust Total Revenue by subtracting $1000 for each day.  Thanks in advance!    

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.

CREATE GLOBAL CUBE statement - losing hierarchies due to slicing

Hi,   I have a SSAS cube from which I generate local cubes with the MDX statement below:   CREATE GLOBAL CUBE MyLocalCube   storage 'MyLocalCube.cub'   FROM [MyCube]  (          MEASURE [MyCube].[Measure1],          MEASURE [MyCube].[Measure2],           DIMENSION [MyCube].[Dimension1],          DIMENSION [MyCube].[Dimension2]          (             LEVEL [HLevel1],                     LEVEL [HLevel2],                     LEVEL [HLevel3],             MEMBER [Dimension2].[Hierarchy1].[HLevel2].&[MemberX]       ),         DIMENSION [MyCube].[Dimension3]  )   The problem is that this statement generates a local cube file which works fine but all other hierarchies defined in Dimension2 are not included. I cannot find the syntax for adding them manually. So, in other w

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 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?  

Is it possible to create attribute relationship across multiple dimensions?

In other data warehouse tools such as Microstrategy, you can create attribute relationship between different dimensions easily. For example, I can define a joint-child relationship(Attr1 from Dim1, Attr2 from Dim2) decide Attr3 from Dim3. In another word, if it is in a natrual hierarchy, it is                                   Attr3                                      |                             (Attr1, Attr2) If we can build such kind of relationship, first Attr3(dim3) is not necessary to appear in fact table. Second, if query without fact data, you can simple pull out Attr1, Attr2 and Attr3 relationship. Is it possible to make such kind of relationship in SSAS? If not, I think put Dim3 in fact table should be an alternative way to relate Attr1, Attr2 and Attr3.    

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



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