I don’t usually like to blog about topics that I think other people have blogged about already, but despite the fact that Mosha blogged about this several years ago (in fact more than eight years ago, blimey) this particular problem comes up so often with my customers and on the MSDN Forum that I thought I should write something about it myself. So apologies if you know this already…
Here’s the problem description. If you define a calculated measure in MDX, that calculation will take place after the real measure values have all aggregated. For example, consider a super-simple cube with a Year dimension, two real measures called A and B and a calculated measure called [A * B] that returned the value of A multiplied by B. In a PivotTable you’d see the following result:
Note how the Grand Total for the [A * B] calculated measure is 12*16=192, and not 12+12+12+12=48. This is expected behaviour for calculated measures, and indeed the way you want your calculations to behave most of the time.
However, there are some scenarios where you want to do the calculation first and then aggregate up the result of that calculation; in our previous example that means you’d get 48 for the Grand Total instead. Currency conversion and weighted averages are common examples of calculations where this needs to happen. How can you handle this in MDX?
Let’s look at a slightly more complex example than the one above. In the following cube, based on Adventure Works data, I created a Date dimension that looks like this:
…and a Product dimension that looks like this:
I also created two measures on a fact table called A and B:
Now, let’s say that once again you want to calculate the value of A*B at the Date and Product granularity, and aggregate the result up. Probably the easiest way of handling this would be to do the calculation in the fact table, or in the DSV, and then bringing the result in as a new real measure. However this may not be possible with some types of calculation, or if the granularity that you want to do the calculation is not the same as the granularity of the fact table.
One way of approaching this in MDX would be to create a calculated measure like this:
CREATE MEMBER CURRENTCUBE.MEASURES.[CALC] AS
SUM(
DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Date])
*
DESCENDANTS([Product].[Category – Product].CURRENTMEMBER, [Product].[Category – Product].[Product])
, [Measures].[A] * [Measures].[B]);
The big problem with this approach (apart from the fact that it may break when you do a multi-select in certain client tools – but you could work around that) is that it is usually very, very slow indeed. Depending on the calculation, it may be unusably slow. So you need a different approach.
This is where scoped assignments come in. If you make a scoped assignment to a real measure, as opposed to a calculated measure, then the value of the assignment will aggregate up outside of the original scope. So, in this case, since you want the calculation to take place at the Date and Product granularity, if you scope on a real measure at that granularity the result of the calculation will aggregate up automatically.
The first step here is to create a new real (ie not calculated) measure for the calculation. This can be done in the DSV by creating a named calculation on your fact table which returns the value NULL:
You then need to create a new real measure on your measure group from this new named calculation:
In this example, I’ve left the AggregateFunction property of the measure to be the default of Sum, but you could use a different setting if you wanted a different type of aggregation. The next step is to process the cube, and once you’ve done that you’ll see a new measure that only returns the value 0:
Next, you need to create the scoped assignment on the Calculations tab of the Cube Editor. If you remember in my post last week about scoped assignments, I recommended avoiding writing scopes using user hierarchies; using only attribute hierarchies the scope statement becomes:
SCOPE([Measures].[A Multiplied By B]);
SCOPE([Date].[Date].[Date].MEMBERS);
SCOPE([Product].[Product].[Product].MEMBERS);
THIS = [Measures].[A] * [Measures].[B];
END SCOPE;
END SCOPE;
END SCOPE;
One very important thing to notice: the sets I’ve used for scoping on the Dates and Products do not include the All Member: for example, [Date].[Date].[Date].MEMBERS. If you use a set that includes the All Member, such as [Date].[Date].MEMBERS, the calculation will not aggregate up correctly.
Here’s the result:
This is going to be much more efficient than the pure MDX calculated measure approach, though just how well the calculation performs will depend on the complexity of the calculation and the size of the area that you are scoping on.
Hi Chris,
Checking your blog is my daily work just like checking emails. I would like to see all your posts whether it is a new topic or a repeated one. Personally, I think your explanation is straight forward, clear and precise. It helps me learn new things as well as remember “already know but nearly forgot” stuff. I’m looking forward to seeing your MDX course in Sydney.
Regards,
George
I think this is MDX design error, it should always take the most detailed data, not the aggregated one, if i want an aggregated i can include measure in a sum function. so A would mean detail A and sum(A) would mean the aggregated one.
Just use SQL level as much as possible for all calculations at the detail level, such as null detection, iif conditions , otherwise all this is done at the aggregated level already. MDX is the most confusing expression language with lots of inconsistencies.
Hi chris
I have calculated memebrs in my cube like this.
– I created dummy columns in my named queries like “calc memb profit”, “calc memb profit2”, “calc memb profit3” etc.. with value as 0.
– Then I calculated values for these columns through scope statement like this
SCOPE ([MEASURES].[CALC MEMB PROFIT]);
THIS = ;
END SCOPE;
this way i have created many calculated measures… and referred them in other calculated measures for example
SCOPE ([MEASURES].[CALC MEMB PROFIT2]);
THIS = iif([MEASURES].[CALC MEMB PROFIT] > x,0,[MEASURES].[CALC MEMB PROFIT1]-[MEASURES].[CALC MEMB PROFIT];
END SCOPE;
some thing like this…
Now these calculations are doing fine, but they take really long time while retrieving results.. (more than 5 mins some times)
Can you please suggest how can I improve performance for these queries.
Thanks in advance
It’s going to be all down to tuning the expressions in the calculated members now, I’m afraid. One thing though: in your IIF you’re returning 0 and you should really be returning null, which may improve performance. Try something like:
THIS = iif([MEASURES].[CALC MEMB PROFIT] > x,null,[MEASURES].[CALC MEMB PROFIT1]-[MEASURES].[CALC MEMB PROFIT];
Thanks a lot for your response chris. I have defined dummy columns in named query SQL, not used new named calculation wizard. If define them as new named calculation, will there be any improvement in performance or both are one and the same.
Hi chris, Thanks a million.. Your null suggestion has improved performance by 80%. I have been struggling with this issue for the past 2 weeks and now its almost fixed. Thanks to you from the bottom of my heart.
Thanks Chris.
I like the idea of using named calculations in the DSV for the new measure; in the past I have created an empty table in the RDB as a source for empty measures to use in scope assignments, but I think the DSV is a better approach. I also concur with George Qiao’s comments.
So glad you took the time to blog this Chris. I have a member from 2 big fact table measures that, I thought, was going to require pre-aggregating and consolidating in the RDBMS (complete redesign). This is a much better solution than the ones I’ve (unsuccessfully) tried implementing the past few days. Most were similar to your calculated member example and were either SLOW or didn’t cover all scopes. This is simple and it preforms extremely well!
Good evening to Mr.Webb and all people reading my post…
I have the same problem Mr. Webb explained here, getting wrong values with grand total with calculated member…(I have a calculated member depending on a PARENT-CHILD Dimension..all works fine except GRAND TOTAL…Both in Excel and Browsing cube…)
I tried to do a test creating a NAMED CALCULATION in DSV and then a real measure basis on it.
But i have problems with creating NAMED CALCULATION..
I have SQL SERVER 2008 R2 Standard Version..
Is it possibile to creare a NAMED CALCULATION in DSV also with this version ???
I do not think so ,,,,Icon in DSV Designer is not active..!!!!
So I tried to insert that named calcolation manually inside the query of fact table…as below..
Select , null AS
from
I was able to deploy all successfully but when i run OLAP CUBE , it gets an error and tell that
the name of calculated name i created is invalid…but the name is very simple is CAMPO1 …(what’s wrong?)
Is it a problem of SSAS ‘Version ???
Thanks in advance.
No, it’s not a problem with the version of SSAS – all versions and editions allow you to create named calculations in the DSV. Something else must be wrong?
Hi, Chris. Great post.
Do you know if there is an easy way to control solve order if you have a number of these named calculations?
The solve order will be dictated by the order that these calculations appear on the Calculations tab of the cube editor
Hi Chris, I have a question.
If I create a calculated measure then the totals are wrong. Now I understand why this is but if I scope the calculation then I can only view the correct aggregation at that scoped level, which makes sense. So how can I create custom calculated measures where I can get the correct total but avoid having to restrict users to slicing by specified attributes to get the total to work. It seems to me like Microsoft have really fallen short of the mark here in terms of implementation of totals. They should really offer the ability to do this special scoping for specific cases but also offer the ability to calculate the total based on the data delivered to the cube ( making the total separate from the cube space ). I don’t know if I am missing something crucial but it just seems as though a calculation involving various measures is far too complicated that what it should be.
Hi Chriss, thanks for a useful explanation of SCOPE definitions in this scenario. I continue to be amazed at how hard it is in this day and age to properly control the timing of calculations. Since this is quite a common issue, making an option to “calculate at pre-aggregation time” should really be something Microsoft implemented. 🙂
Anyway.
First I would like to comment that there is a third option as well that may fit some similar scenarios: Deploying hidden calculated members and then using SOLVE_ORDER. There is a decent article about it on TechNet: http://technet.microsoft.com/en-us/library/ms145539.aspx
Secondly I would like to ask a question: Can you explain why your proposed solution here requires an “empty measure” in the physical data model? I have a scenario where the physical data model and DSV are outside of my control (at least directly; I can create new user-definded objects in the DSV but not edit existing ones). Is there another way I can use your solution, which does not require making changes to the source tabel or DSV?
Hi Jesper,
Actually there are a lot of good reasons not to pre-calculate calculations like this – it would make the cube very large and processing very slow in a lot of cases.
This solution needs an empty, physical measure because it does not do any of the aggregation in MDX – it relies on way that physical measures aggregate up automatically. This is much faster than aggregating the result of the calculation in MDX. There is no way of using this approach without making changes to the source table or DSV, but if you can create new objects in the DSV you can create a named calculation that returns null and use that for your measure.
Great. So I can use an object from a DSV object other than the actual datasource? For example create a new DSV object called “ChrissRules” and use that in my Scope definition? Does the object need to be related to the datasource object?
I’m not quite sure what you’re saying here – you do not want to create a new Data Source VIew, but you can create a new named calculation in your existing DSV and build your measure from that. A DSV is not the same thing as a data source.
Sorry I wasn’t more clear 🙂 What I mean by “data source” is the source database from which a table in the DSV reads data. So what I am getting at is this: Can I create a new table in my existing DSV, name it “ChrissRules”, and insert my named calculation there? Or does my named calculation have to be inserted into the named query which actually reads my source data? All existing named queries in my setup are machine-generated, and any changes I make in them are routinely overwritten. Does this make more sense? 🙂
Yes, I understand now, thanks! To add a new measure to an existing measure group, your named calculation needs to be on the named query that your measure group uses. If your DSV is machine generated, that will be a problem.
Hi Chris
Firs of all thanks for the help you are providing to community.
While building a cube I need to calculate the cumulative sum with one exception. the measure should show the cumulative sum over all the dates except 19000101 which is used for not applicable. when I use
AGGREGATE(
{NULL:[Contract End Date].[Calendar Dates].CurrentMember}
, [Measures].[AVL Amount])
it gives me cumulative for all the periods including 19000101 which is wrong as we want to exclude the amounts for 19000101. I have modified it to look like this:
CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL End Date Amount] as “NA”;
SCOPE ( {[Measures].[Cumulative AVL End Date Amount]});
SCOPE (EXCEPT([Contract End Date].[Calendar Dates].[Date].MEMBERS
, {{[Contract End Date].[Calendar Dates].[Date].&[19000101]}}) );
THIS = IIF(ISEMPTY([Measures].[AVL Amount])
,NULL
,AGGREGATE(
{[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
* {NULL:[Contract End Date].[Calendar Weeks].CurrentMember}
, [Measures].[AVL Amount])
);
END SCOPE;
END SCOPE;
now it calculates cumulative correctly but only on the date level when I select another level it shows NA.
Can you please advise how It can be done for all the date levels and all the time hierarchies.
Thanks in advance.
Regards
Harris
Hi Harris,
I think there’s an easier solution here. I recommend adding a new attribute to your Contract End Date dimension called IsFirstDate that is true for the date 19000101 and false for all other dates. You can avoid the scoped assignment and write your calculation as follows:
CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL End Date Amount] as
IIF(ISEMPTY([Measures].[AVL Amount])
,NULL
,AGGREGATE(
{[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
* {NULL:[Contract End Date].[Calendar Weeks].CurrentMember}
, ([Measures].[AVL Amount],[Contract End Date].[IsFirstDate].&[False])
);
This should give you the cumulative sum without the 19000101 date and avoids the need for a scoped assignment.
Hi Chris
Thanks for a quick reply.
I have created a flag on date dimension and set its value true for 19000101 and false for all the other dates.
i have modified the calculated measure as:
CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
AS IIF(ISEMPTY([Measures].[AVL Amount])
,NULL
,AGGREGATE(
{[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
* {NULL:[Create Date].[Calendar Weeks].CurrentMember}
, ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
);
it is showing nulls every where for both Calendar Dates and Calendar Weeks hierarchies.
Regards
Harris
Could be a number of things. Can you first try:
CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
AS IIF(ISEMPTY([Measures].[AVL Amount])
,NULL
,AGGREGATE(
{NULL:[Contract End Date].[Calendar Dates].CurrentMember}
* {NULL:[Create Date].[Calendar Weeks].CurrentMember}
, ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
);
CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
AS IIF(ISEMPTY([Measures].[AVL Amount])
,NULL
,AGGREGATE(
{NULL:[Contract End Date].[Calendar Dates].CurrentMember}
* {NULL:[Create Date].[Calendar Weeks].CurrentMember}
, ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
);
Tried this but the same output i.e. null
Can you see what happens without the Calendar Weeks hierarchy in the calculation? ie
CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
AS IIF(ISEMPTY([Measures].[AVL Amount])
,NULL
,AGGREGATE(
{NULL:[Contract End Date].[Calendar Dates].CurrentMember}
, ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
);
I have already tried to remove the calender weeks hierarchy bu the result was same.
I have also tried
CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
AS IIF(ISEMPTY([Measures].[AVL Amount])
,NULL
,AGGREGATE(
{[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
, [Measures].[AVL Amount])
);
It gives me correct cumulative numbers but shows the value for 19000101 equal to base measure i.e. [Measures].[AVL Amount], but the cumulative is only calculated on Date level when I roll up to Month, quarter or year level it shows nulls
Regards
Harris
OK, it could be that you have some kind of attribute overwrite problem here that will be very difficult to debug remotely. Another approach could be this:
CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL End Date Amount] as null;
SCOPE ( {[Measures].[Cumulative AVL End Date Amount]});
SCOPE (EXCEPT([Contract End Date].[Contract End Date].[Contract End Date].MEMBERS
, {{[Contract End Date].[Contract End Date].[Contract End Date].&[19000101]}}) );
THIS = IIF(ISEMPTY([Measures].[AVL Amount])
,NULL
,AGGREGATE(
{[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
, [Measures].[AVL Amount])
);
END SCOPE;
SCOPE([Contract End Date].[Contract End Date].[All]);
THIS = TAIL(EXISTING [Contract End Date].[Contract End Date].[Contract End Date].MEMBERS, 1);
END SCOPE;
END SCOPE;
I’m assuming here that the attribute hierarchy that the date level of your Calendar Dates user hierarchy is based on is called [Contract End Date].[Contract End Date].
HI Chris
I have tried this solution it gives correct cumulative on the date level but again nulls on all other levels.
Meanwhile after a hit and trial I have found a solution which is working as expected. Here is the mdx:
CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
AS IIF(ISEMPTY([Measures].[AVL Amount])
,NULL
,AGGREGATE(
{NULL:[Contract End Date].[Calendar Dates].CurrentMember}
* {NULL:[Contract End Date].[Calendar Weeks].CurrentMember}
* {[Contract End Date].[First Date Flag].&[False]}
, [Measures].[AVL Amount])
);
Now I have to calculate cumulative on other role playing dimensions too. I can create separate calculated measures but then I have long list of measures. I want to calculate single measure for all role playing dimensions. if i use following mdx it gives wrong calculation:
CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL Amount]
AS IIF(ISEMPTY([Measures].[AVL Amount])
,NULL
,AGGREGATE(
{NULL:[Create Date].[Calendar Dates].CurrentMember}
* {NULL:[Approval Date].[Calendar Dates].CurrentMember}
* {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
* {[Create Date].[First Date Flag].&[False]}
* {[Approval Date].[First Date Flag].&[False]}
* {[Contract End Date].[First Date Flag].&[False]}
, [Measures].[AVL Amount])
);
I have seven role playing dimensions I just showed three above for understanding purpose.
I have checked separate measures and they are working fine but not one measure for all.
Do you see any possibility for this or its better to use separate measures for each role playing dimension.
Regards
Harris
I recommend you create separate measures for each role playing dimension – that will keep things simple, and you would get some potentially strange results if you tried to create one measure for all your dimensions.
tHANKS Chris,
I’m having he following problem. I have two fileds C8 and C9 and i have a calculation AvgAbandonTime=C8/C9. There is a location dimension and it gives me the values based off it. Please see the data set below. But in the grand total insted of the average its giving me sum(c8)/sum(c9). Then i followed your process. Created a real measure in the DSV and followed the above procedure, but still getting the same result.
Location AvgAbandonTime C8 C9 C8DIVC9
CITY 92 11588 126 92
COUNTY 16 157 10 16
DIV 73 62160 857 73
GrandTotal 74 73905 993 74
Can you post the code you are using?
Hi Chris,
How it will work with Parent-Child Hierarchy?
I have Country dimension (Parent – Child Hierarchy)
Below sample code I m using :
SCOPE ([Measures].[Converted Lost Value]);
SCOPE([Countries].[Countries].Members);
THIS = SUM(DESCENDANTS([Countries].[Countries].CurrentMember,[Countries].[Countries].[Level 05]),
([Measures].[Out of Conformity Value] * ([FE Date].[Fiscal].CurrentMember, [Measures].[Exchange Rate])));
END SCOPE;
END SCOPE;
It should work fine with a parent-child hierarchy, you just need to scope on the key attribute of the dimension.
Hi Chris,
I need to add Customer dimension to have the same behaviour as item. How do I add this into the scope.
Thanks
Please disregard my previous question. I guess I need to just add
SCOPE([Measures].[A Multiplied By B]);
SCOPE([Date].[Date].[Date].MEMBERS);
SCOPE([Client].[Client].[Client].MEMBERS);
THIS = [Measures].[A] * [Measures].[B];
END SCOPE;
END SCOPE;
END SCOPE;
I think what you need is this:
SCOPE([Measures].[A Multiplied By B]);
SCOPE([Date].[Date].[Date].MEMBERS);
SCOPE([Client].[Client].[Client].MEMBERS);
SCOPE([Product].[Product].[Product].MEMBERS);
THIS = [Measures].[A] * [Measures].[B];
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
Hi Chris and thank you for your quick answer. I really appreciate it since this is kind of an urgent issue.
Your recommendation will work fine if user chooses date, client and product at the same time.
What I want is to give them ability to use either date – client or date – item option and aggregate total on both levels. In this case it does not provide correct result.
What my code should do is aggregate from the date, client *and* product granularity in all cases, regardless of what is chosen. I think what you want is to aggregate from client only if client is chosen, but this means you run into a common problem with advanced MDX calculations: something is always chosen from a hierarchy all the time! For this reason I always recommend to my customers that trying to write calculations that do something different depending on what has been chosen should be avoided: it is possible to do this up to a point, but the code is complex, usually slow, and the results turn out to be confusing for end users. It’s better to have different calculations for different scenarios.
Hm, I need exactly the same behaviour regardless of what user selected (date, client and / or item). I did some testing and it did not look right. Will probably need to do more testing.
I think I got you. I should have 2 calculations then. One for Date / Client scope and another for Date / Item scope right?
Yes, exactly
Hi Chris,
It does not work when I try to add more dimensions to have the same calculation:
SCOPE([Measures].[A Multiplied By B]);
SCOPE([Date].[Date].[Date].MEMBERS);
SCOPE([Product].[Product].[Product].MEMBERS);
THIS = [Measures].[A] * [Measures].[B];
END SCOPE;
END SCOPE;
END SCOPE;
SCOPE([Measures].[A Multiplied By B]);
SCOPE([Date].[Date].[Date].MEMBERS);
SCOPE([Client].[Client].[Client].MEMBERS);
THIS = [Measures].[A] * [Measures].[B];
END SCOPE;
END SCOPE;
END SCOPE;
Is the second scope overwrites the first one.
Thanks
Hello Chris,
I have exactly the same problem like Alexandar that the second scope overwrites the first one for 2 time hiearchies:
Scope([Measures].[NSLY Null]);
Scope([Dim Time Week].[Week Date].[Week Date].Members);
This = (ParallelPeriod([Dim Time Week].[TimeHierarchyWeek].[Week Year],1,[Dim Time Week].[TimeHierarchyWeek].CurrentMember),[Measures].[Net Sales Incl VAT]);
end scope;
Scope([Dim Time].[Date].[Date].Members);
This = (ParallelPeriod([Dim Time].[TimeHierarchy].[Year],1,[Dim Time].[TimeHierarchy].CurrentMember),[Measures].[Net Sales Incl VAT]);
end scope;
end scope;
If measure A and measure B are in two different fact tables, such that
Measure A is associated with Dimension X
Measure B is associated with Dimension Y
then the scope mdx doesn’t seem to work.
It is possible to write an assignment that does a calculation on two measures from different fact tables. What do you want to do though?
I also need product of measures available in the two fact tables. I can create a 3rd fact table based on the 2 existing fact tables but that would create a very big catesian product fact table, which is what I want to avoid.
Fact1 table –> Country, State, Plan, Price
Fact2 table –> Country, State, City, Street, Qty
I need a calculated measure for Price*Qty
Thanks,
AJ
This article might help you: http://consultingblogs.emc.com/christianwade/archive/2006/07/25/Measure-expressions_3A00_-how-performant-are-they_3F00_.aspx
You can do what you want with scoped assignments, but as Christian shows a measure expression might be a better option (if you have Enterprise Edition)
Hi Chris,
Regarding the grand total issue,
I have close to 15 dimensions in my cube and I have 7-10 calculated measures that are of the form
[Measures].[A] * [Measures].[B] or [Measures].[A] / [Measures].[B]
These calculated measures can be seen from all dimensions. Should the scope statement you have mentioned include all the attributes of all the dimensions as users will be using them from the pivot fields?
If so, the calculation code will be so huge. Also the option where you have suggested to write specific scopes for specific combination also will turn out to be huge as combinations for 15 dimensions are so many
Can you please suggest an approach?
Thanks
Uday
You could use the Leaves() function here, but in this case why don’t you just calculate the values in your fact table in SQL Server (or whatever RDBMS you’re using)? It would give you the same result.
Hi Chris,
Thanks for your reply.
But does every calculated measure need to be created in sql server or dsv. In my case the calculated measure depends on two different fact tables. Can the cube not handle this via MDX?
Thanks
Uday
No, certainly not – it’s only in the special cases that this post describes that you need to create calculations in this way. Most calculated measures in MDX are calculated after the real measures have been aggregated – only a few types of calculation need to be aggregated after the calculation has taken place.
I had the same issue where end users were publishing “sales price” and “quantity” to the cube. At the input level the “sales amount” = “Sales Price” * “Quantity”. This needs to be handle as a two pass calc. So all I did was scope at the child level of the Dimension ID that linked from the hierarchy table to the measure table. Did this calc then did the calculate. Worked great. Here’s what it looks like. If you have more dimension tables that are at a lower level of granularity I would assume you just add their attributes keys to this scope statement
SCOPE ([Detail].[Forecast Id].children);
[Measures].[Sales]
= IIF([Measures].[Qty]=0,NULL,[Measures].[Selling Price] * [Measures].[Qty]);
END SCOPE;
CALCULATE;
Chris, Thanks for this magnificent post as usual, I’ve really benefited from it.
However I couldn’t understand why to use a real measure not a calculated measure. That part of the post was not clear to me. would you please explain it more? especially what do you mean by “the value of the assignment will aggregate up outside of the original scope” ? what is the original scope? can you please make it simpler?
Thanks again 🙂
It has to be a real measure because only real measures aggregate up – calculated measures don’t. Think of a very simple hierarchy with an All Member and two members, A and B, beneath it. For a real measure, the All Member is the aggregated value of A and B; this is not the case for a calculated measure.
To answer the second part of your question, using the same hierarchy as in the previous paragraph, if you create a scoped assignment on member A then for a real measure the All Member’s value will change so that it is still the aggregated value of A and B.
Thanks Chris, just to make sure that I’ve understood you correctly. Do you mean that if the wanted measure = [measure X]*0.5, and using your example hierarchy:
– In real measure case, the All Member = (([A],[measure X])*0.5) + (([B],[measure X])*0.5)
– While in calculated measure case, the All Member = ([ALL],[measure X])*0.5
Is this what do you mean? and, does any case (real or calculated measure) has better performance?
Yes, you are completely correct. Regarding performance, the calculated measure will perform better because it is doing less work.
nice post Chris! I’ve recently created a new SSAS cube with SSDT 2012 against SQL2012 and got the red squiggly line under the first word of “scope” (like your screenshot in your post). Due to this problem, the cube can’t be deployed. If I copy the same scope statement to SSDT 2010, it seems no problems. I don’t know have you run into this issue before, I can’t image I am the only one has this problems, not sure if I should use SSDT 2010 instead of SSDT 2012. Thanks.
I’ve never been prevented from deploying by a bug like this. What error message do you see from SSDT? Are you sure there isn’t a syntax error somewhere?
Hi Chris,
Pretty new to MDX here and have found you’re blog to be great reading and very helpful.
I have a weighted average calculation that does ((A * B ) / A) but only where A is greater than 0. I’ve been trying to determine if I can leverage what you did with the SCOPE statement but I’m having trouble with excluding the data I don’t want included in the calculation.
Can I do that with what you demonstrated above or am I barking up the wrong tree?
Thanks.
I forgot to mention that B is actually the result of two other measures being divided to get a percent (B = C / D).
Thanks.
Yes, using a SCOPE statement would be the way to go. Define B as a regular calculated measure, create your dummy ‘real’ measure for the weighted average, then overwrite it with a SCOPE statement something like this:
SCOPE(MyDim.MyHierarchy.MyLevel.Members, Measures.WeightedAverage);
THIS = iif(Measures.A=0, null, ((Measures.A*Measures.B)/Measures.A));
END SCOPE;
Hi Chris,
After being sidelined by another project, I’m back trying to get my weighted calculation to work. I think I’m close but my weighted average ends up as a sum instead of a percent. I’m probably missing something obvious.
The calculations I need are based on 3 measures:
* Inspections (I)
* Passing Inspections (P)
* Volume (V)
Volume is in a different fact table than the inspection data.
Average Quality (AQ) is defined as P / I. I created a calculation in the cube for this.
Weighted Average Quality (WA) is defined as ((V * AQ) / V) where there is an AQ value. I’ve created this as a measure in the source.
Some sample data.
Date I P V AQ
02/01/2015 4 3 100 75.00%
02/02/2015 1 1 7 100.00%
02/03/2015 0 0 11 N/A
Based on the sample data, my Weighted Average should be ((100 *.75) + (7 * 1)) / (100 + 7) = ( 75 + 7 ) / 107 = 82 / 107 = 76.64%
The SCOPE statement I’m using is
SCOPE([Measures].[WA]);
SCOPE([Date].[YQM].[Date ID].members);
THIS = iif
( [Measures].[AQ] = 0
, NULL
, ( [Measures].[V] * [Measures].[AQ] ) / [Measures].[V]
);
END SCOPE;
END SCOPE;
There is not ALL member at this hierarchy level [Date].[YQM].[Date ID]. The Weighted Average I get for the sample above is 175.00%.
Can you help point me in the direction of what I’m doing wrong. Should I be using the SCOPE on the component pieces to exclude the rows I don’t want as part of the Weighted Average?
Also, can you recommend a better way to test SCOPE statements outside of reprocessing the cube? It doesn’t seen to work with SSMS.
Thanks.
Hi Tom,
I think the mistake you’re making is that your scope statement is summing up the result of the whole calculation, whereas you actually want to sum up just the result of V * AQ and then divide the result by V. Something like this should work:
SCOPE([Measures].[WA]);
SCOPE([Date].[YQM].[Date ID].members);
THIS = iif
( [Measures].[AQ] = 0
, NULL
, ( [Measures].[V] * [Measures].[AQ] )
);
END SCOPE;
END SCOPE;
CREATE MEMBER CURRENTCUBE.MEASURES.WA1 AS
IIF(MEASURES.V = 0, NULL, MEASURES.WA/MEASURES.V);
The calculated measure WA1 should give you the result you need.
Chris I have a calculated measure that returns an average price by dividing an amount measure by a quantity measure. The value of the calculated measure is correct at the day level but not for the total for the month because its actually showing the average of the daily calculated measure members rather than the result of the average price calculation using the totals of the amount and quantity measures for the whole period?
How are you doing your calculation? It should be fairly straightforward, something like:
CREATE CURRENTCUBE.MEASURES.MYCALC AS IIF(MEASURES.[QUANTITY]=0, NULL, MEASURES.[AMOUNT]/MEASURES.[QUANTITY]);
Chris thank you for your response. It turned out that I had an error in my calculation and once I had corrected it the normal aggregation behavior returned the correct results. During the process of resolving my issue I watched one of your webcasts where you used a “Deploy script” button to push calculations to the cube without having to do a full deploy and process of the project. After doing a little more research I found that this functionality is part of “BIDS Helper” and available for download from Codeplex. This discovery was invaluable as it made it so much easier to debug the calculated measure script. So thanks again for your help and please keep up the excellent work!
Dear Chris
I don’t have many experience in MDX and starting in this new world I have learned too much from your blog in order to solve many problems that I have found during the process and want to give the thanks for excelent work that you have done.
In this case, I’d like to receive your help in this calculation that is driving me crazy:
I have a
– Geography dimension (Country -> State -> City)
– 2 related Fact Tables: “Revenues” (with measure Revenue) and “Revenue Goal” (with measure Revenue Goal) and created
– A dummy “Real” measure into the Revenue Fact table (in the dsv) to calculate the Revenue not planed (by city) using Scope, this is called “NotPlannedRevenue” with null like default value.
Later using Scope I have calculated the values in order to get correctly Totals Values over the a Geography hierarchy which is working properly.
The calculate consists in aggregate the revenues from a city if the revenue goal is cero. The MDX script for the measure is
SCOPE([Measures].[NotPlannedRevenue]);
SCOPE ([Geography].[GeoHier].[City].Members);
THIS=iif([Measures].[Revenue Goal]=0 , [Measures].[Revenue] ,0 );
END SCOPE;
SCOPE( [Geography].City.[All]);
This =sum( Descendants([Geography].[GeoHier].CurrentMember,,LEAVES),[Measures].[NotPlannedRevenue]);
END SCOPE;
SCOPE( [Geography].State.[All]);
This =sum( Descendants([Geography].[GeoHier].CurrentMember,,LEAVES),[Measures].[NotPlannedRevenue]);
END SCOPE;
END SCOPE;
The “Totals” are well calculated, but the issue is when I filter Cities from the Geography dimension, the total values are not being calculated using only the selected cities, it use all the cities from the fact table. I have problems in the totals by City, State, Country.
On this URL you cand find screenshots from te data:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cea11a09-6173-43bd-a263-9c1a264eb0ff/totals-calculation-issue-when-i-filter-member-used-in-an-scope-calculation-with-descendant?forum=sqlanalysisservices
I appreciate your help to solve the way which I must write this MDX or maybe I have to use another strategy., Thanks!
Chris as you suggested on Microsoft forum, I removed 2 SCOPE statements (on the All members of City and Country) and the calculation works properly.
Thanks too much!
Hi Chris,
I would appreciate, if you could explain one thing about SCOPEd assignments. It is related to currency conversion. Since I have many-to-one conversion, I’m trying to do it in ETL: in my fact table for transactions I have 2 columns – one for original amount and one for amount in EUR. Then, I have 2 corresponding measures in my cube: [Amount] and [Amount EUR]. I have Reporting Currency dimension with 2 members EUR and Local. Then, in my calculations, I have, for example:
Scope (Measures.[Amount]);
Scope([Reporting Currency].[EUR]);
Scope(Measures.[Amount]);
This = Measures.[Amount EUR];
Non_Empty_Behavior(This) = Measures.[Due Amount];
End Scope;
End Scope;
End Scope;
I.e., I simply assign [Amount EUR] to [Amount]. Is this a good way? I mean, would it affect performance anyhow? For example, if one of the users selected EUR as reporting currency, and another one selected Local currency at the same time in some report? What happens in the cube in this case?
I have never seen any explanation for such cases, so I do not know if it is right or wrong way.
There is always going to be some overhead using scoped assignments or indeed any MDX calculation, but this is very simple so you probably won’t notice any overhead. You will need to make sure that the IsAggregatable property is set to false on the Reporting Currency hierarchy, though. Why do you scope on Measures.Amount twice? You can probably delete the second scope statement.
Also, remember that Non_Empty_Behavior is deprecated and so you probably shouldn’t use it:
https://cwebbbi.wordpress.com/2015/01/27/deprecateddiscontinued-functionality-in-ssas-2014/
Wow, that was fast! Thank for explanation.
IsAggregatable set to False for Reporting Currency, indeed. I have SSAS 2012, so Non_Empty_Behavior shall still be there, right? There is a typo, there should be Non_Empty_Behavior(This) = Measures.[Amount];
I have a double scope on Measures.Amount because it is similar to what BI wizard offers when doing currency conversion in the cube. I could never understand, why it wants to scope twice on measures, but thanks anyway for pointing out.
Still, currency conversion in the cube shall perform worse that one in ETL, right? I mean, this is what BI wizard offers me for many-to-one conversion:
Scope ({Measures.[Amount] });
Scope( Leaves([Dates]) ,
[Reporting Currency].[EUR],
Leaves([Currencies]));
Scope({Measures.[Amount]
});
This = [Reporting Currency].[Local] / Measures.[Exchange Rate];
End Scope;
End Scope;
Format_String(This) = “# ### ##0.00”;
End Scope;
Is ETL way better than doing currency conversion in the cube?
Yes, if you are doing many-to-one currency conversion, doing the conversion in ETL is the right thing to do – it will give you faster query performance than if you did it in MDX.
Hello ,
My problem is like below
Tier Sale Avg
TierA 25 150
TierB 35 165
TierC 42 146
TierD 35 175
Total 137 636
Grand total 0.215408805
I have dimension tier and two measure sale and Avg. my requirement is as below
I want to display Grand total as below 137/636 =0.21540885 in excel
Please help
Regards,
sanjeevan
Which measure do you want this calculation to appear as the Grand Total for? If it’s for Sale, you could do something like this:
SCOPE(MEASURES.[SALE], [Tier].[Tier].[All Tier]);
THIS = IIF(MEASURES.[AVG]=0,NULL,MEASURES.[SALE]/MEASURES.[AVG]);
END SCOPE;
This scopes on the All Member on the Tier hierarchy and the SALE measure, and replaces the value with the result of the calculation.
Thanks so much for your help. I figured I would need to do the sums via the SCOPE and then apply the formula but you saved me a lot of trial and error.
Hello Chris ,
I want to write MDX
my Scenerio is like below
Lets have
Month Item Cost
Jan AA 10
JAN BB 20
JAN CC 30
FEB AA 15
FEB CC 20
MAR AA 70
MAR BB 52
I need output like belo
Item Cost
AA 70
BB 52
CC 20
Means last non empty value of particular Item .
Regards,
Sanjeevan
Have you tried using a normal, semi-additive measure that returns the last non-empty value? See https://msdn.microsoft.com/en-us/library/ms175356.aspx?f=255&MSPPError=-2147217396
Or maybe https://cwebbbi.wordpress.com/2011/03/24/last-ever-non-empty-a-new-fast-mdx-approach/
I tried this approach but its not working for , Can you provide me sample query
It would be hard to provide a sample query (over what’s in the blog post) without having access to your cube.
Thanks Chriss !!!
I am able to achive the solution of my query mentioned above by using the code below ,you mentioned in your post
MEMBER MEASURES.[Last Sale Original] AS
TAIL(
NONEMPTY({NULL:[Date].[Date].CURRENTMEMBER} * [Measures].[Internet Sales Amount])
).ITEM(0)
Once again thanks
Hello Chriss,
My problem is like below
I have to native measure [Internet Extended Amount],[Internet Freight Cost], I sliced it with Product model
my Grand Total Should be $566.57 for [Internet Extended Amount] measure
Grand Total= (Internet Extended Amount * Internet Freight Cost)/SUM ( Internet Extended Amount for selected Model name )
Values
Row Labels Internet Extended Amount Internet Freight Cost
Bike Wash $7,218.60 $180.51 $11.93
Classic Vest $35,687.00 $892.18 $150.75
Cycling Cap $19,688.10 $492.31 $55.22
Fender Set – Mountain $46,619.58 $1,165.49 $348.67
Grand Total $1,09,213.28 $2,730.49 $566.57
Grand Total $566.57
Thanks
Sanjeevan
Hello. I need to create a calculated member. I have a dimension called GEO with 4 levels (Geo, Sub-Geo, Country and Sub-Country). When the Country is US I need to use a Measure X, but for all the another countries I need to use Measure Y. This should apply even if I am in the ALL level. I tried something like
IIF (Country.CurrentMember = US, Measure X, Measure Y). It works only for the Country level, but when the aggregations is in ALL level or another Level like Sub-Geo, the calculated member always use Measure Y.
It sounds like you need a SCOPE statement here. Check out this video for an introduction to the topic: https://sqlbits.com/Sessions/Event8/Fun_with_Scoped_Assignments_in_MDX
Hi Chris,
I’m pretty sure that the explained solution would work as well for my case but I don’t retrieve the expected result.
First of all, the model is as follow: 1 fact table “Sales”, 1 regular dimension “Product”, 1 intermediate fact table “Product_Market” and 1 many to many dimension “Market”.
The fact table “Sales” contains a measure “Quantity” that we need to mutliply by the measure “Factor” from the intermediate fact table.
FYI: The factor can be different for the set Product-Market. This is why we do not have the “Factor” measure in the “Sales” fact table.
Could you tell me whether your proposed solution can work with a many to many?
Thanks and regards,
Jérémie
Yes, it should work I think; your calculation is basically the same as a currency conversion. If you’re using Enterprise Edition you’ll find it’s much easier to use Measure Expressions for your calculation if it’s just a simple multiplication or division.
Dear Chris,
we have implemented the proposed solution but we are fuguring out a behaviour that we cannot explained sa far. Maybe could you read the below SCOPE and tell me whether you see what the root cause would be. Thanks in advance.
With this SCOPE, the value of the calculation on each level is 0:
SCOPE([Measures].[Unit Multipliedby Factor]);
SCOPE([Sales date].[Sales date – key].[Sales date – key].MEMBERS);
SCOPE([Product market].[Product market by market – level10].[Product market by market – level10].MEMBERS);
THIS = [Measures].[units] * [Measures].[market factor];
END SCOPE;
END SCOPE;
END SCOPE;
With this scope, the value of the calculation is OK on lower level but not on higher level:
SCOPE([Measures].[Unit Multipliedby Factor]);
SCOPE([Sales date].[Sales date – key].MEMBERS);
SCOPE([Product market].[Product market by market – level10].MEMBERS);
THIS = [Measures].[units] * [Measures].[market factor];
END SCOPE;
END SCOPE;
END SCOPE;
What I understood from your explanation it that using sets for scoping on the Sales date and Product market must not include the All Member. SO the 1st SCOPE statement should meet this requirement where the second not. What we do not understand is why the first statement returns 0.
Thanks again in advance for your support.
Jérémie
The first MDX should be correct – each of the hierarchies used is an attribute hierarchy, isn’t it?
Also, [Measures].[Unit Multipliedby Factor] must *not* be a calculated measure.
The attribute hierarchies are well used in the first MDX.
[Measures].[Unit Multipliedby Factor] is a measure in the Sales Measure Group. Not a measure in the Calculations tab.
We continue the investigation…data type requirement?
Thanks! It works very well
Hi Chris,
Again, I have a problem that requires a non-trivial approach with scoped assignments. The situation is following: I have a single measure Saldo (Sum) and Countries dimension in my cube. The Countries dimension contains plain country members, and also a Group member, which contains the sum of all countries. Of course regular [All] member contains duplicate amount if i select any country and Group member, because Group already includes this country.
Now, what I’m trying to achieve is following, in Excel at least:
1) If Group member is selected (rows, columns, filter), the total for Saldo should be equal to the Group member value.
2) If Group member is not selected, the total for Saldo should be equal to sum of selected countries.
Of course, I’m trying to achieve this with scoped assignments. I created an additional empty physical measure Non Group Saldo, and assign value to it as following:
Scope(Leaves([Countries]));
Scope([Measures].[Non Group Saldo]);
If NOT [Countries].[Country Name].CurrentMember IS [Countries].[Country Name].[Group] THEN This = [Measures].[Saldo] END IF;
End Scope;
End Scope;
Then, for #2, I do it like this:
Scope(Root([Countries]));
Scope([Measures].[Saldo]);
FREEZE([Measures].[Non Group Saldo]);
This = [Measures].[Non Group Saldo];
End Scope;
End Scope;
#2 works fine, but how to make #1 work as I want? Is that possible with scoped assignments?
Let me illustrate #1 and #2.
#1:
USA 100
Canada 70
Group 500
————-
Total 500
#2:
USA 100
Canada 70
————-
Total 170
Of course, i can change dimension design and make it parent-child, but I would like to avoid it, because I’m not the original developer.
I found the solution, may be useful to someone.
First, i added a calculated member Group Saldo:
CREATE MEMBER CURRENTCUBE.[Measures].[Group Saldo]
AS Aggregate([Countries].[Country Name].[Group], [Measures].[Saldo]),
VISIBLE = 0;
Then, i added one physical empty measure Group Detected as Sum, and a scope assignment for it:
Scope([Countries].[Country Name].[Group], [Measures].[Group Detected]);
This = 1;
End Scope;
Which basically means that if a Group member is selected, [Measures].[Group Detected] equals 1 on [All] level as well.
Then, I changed #2 as following:
Scope(Root([Countries]));
Scope([Measures].[Saldo]);
FREEZE([Measures].[Non Group Saldo]);
This = [Measures].[Non Group Saldo];
IF [Measures].[Group Detected] = 1 THEN This = [Measures].[Group Saldo] END IF;
End Scope;
End Scope;
Which means that i check if Group member is in selection and set Total for Countries to Group Saldo in this case.
Hi Chris,
I need one MDX script
Below is the relational query
Select COUNT(distinct empID) from Emp
Where EmpType >10
I have Employee Dimension with attribute empID ,EmpType
I created distinct count (Employee.EmpID.EmpID) as Measure
How can i get the distinct empID count using EmpType in MDX
Hi Chris,
I need one MDX script
Below is the relational query
Select COUNT(distinct empID) from Emp
Where EmpType >10
I have Employee Dimension with attribute empID ,EmpType
I created distinct count (Employee.EmpID.EmpID) as Measure
How can i get the distinct empID count using EmpType in MDX
You’d need to do something like this, I think:
SCOPE(MEASURES.[EMP DISTINCT COUNT], NULL : EMPLOYEE.EMPID.&[9]);
THIS = NULL;
END SCOPE;
Hi Chris,
I need one calculated member in my cube below i the scenario please help me.
I have one dimension with attribute name as Status Type it has values 9,10,11,12,13,14,15 and i have one calculated measure as distinct count of (ID)
Now i need to calculate distinct count (ID) based on Status type below are the requirements for calculation
1)if dimension.status type.statustype value >10 then i need distinct count(ID)
2)if status type <=10 then i need distinct count(ID)
Please help me..
SCOPE(MEASURES.[EMP DISTINCT COUNT], NULL : EMPLOYEE.EMPID.&[9]);
THIS = NULL;
END SCOPE;
This expression not working as expected it display the Null for only status type 10 ,rest of the status type values not chaged even though 9 also populated the value as per the requirement 9 shoud be null
Is there any way to write conditions for Dimension attributes in MDX ?
Is there any way to write less than and greater than conditions for Dimension attributes in MDX ?
Hi Chris
Great post, thanks.
I have a question please which I think this post answers but I wanted to check.
I have 2 fact tables at different grains and need to create a calculated measure using them both.
CallSummary (EmployeeKey, ApplicationCodeKey, CallDateKey, AvailableMinutes)
ProductiveTime (EmployeeKey, DateKey, ProductiveHours)
My calculated measure = AvailableMinutes/ProductiveHours.
So this calc will only be accurate from the Employee, Date level upwards. Can I just create the calc and scope it ? (Im new to SSAS so will have to play around with getting the scoping correct). Or do i need to create a named query over the CallSummary table and aggregate it up to the EmployeeKey, CallDateKey level and then use this new table for my measure group and calculated measure? (Or create this new summary table in the ETL)
Many thanks
Garry
Hi Gary,
You need to scope the calc from Date and Employee yes – bit you don’t want to aggregate the results of the calculation I think (ie don’t do what I’m doing in this blog post, you don’t ever want to sum up the result of Productive Time). So just create a calculated measure that returns null and then overwrite it with a scope statement something like this:
create member currentcube.measures.productivetime as null;
scope(measures.productivetime, Employee.EmployeeKey.members, Date.Datekey.members);
this = iif(measures.productivehours=0, null, measures.availableminutes/measures.productivehours);
end scope;
No need to create a named query or any regular measures.
Thanks Chris, that was a great help.
The calculation is working on EmployeeKey but not on the others? My scope looks like this:
CREATE MEMBER CURRENTCUBE.[Measures].[AMPH] AS Null, VISIBLE = 1 ;
SCOPE([Measures].[AMPH], [Employees].[Employee Key].members, [Rosters].[Start Date Key].members, [Call Date].[Date].members);
this = iif(
[Measures].[Productive Minutes] = 0,
null,
([Measures].[Available Time] / 60.0) / ([Measures].[Productive Minutes] / 60.0)
);
BACK_COLOR(THIS) = RGB(255,128,64); –Orange
END SCOPE;
I think its because the example I sent you I had simplified so I could understand the theory. Looking at the above you will see I have scoped to [Rosters].[Start Date Key].members. This is because my fact table that I mention as such: ProductiveTime (EmployeeKey, DateKey, ProductiveHours), actually has a RosterKey and not a DateKey. The RosterKey joins to a Roster dimension table that has the DateKey on it. The DateKey joins to the Date dimension table (snowflake schema). That said, I am including the Date in my Roster dimension as an attribute instead of having another Date cube dimension (I have a lot of dates and so having a separate cube dimension each time becomes messy for the user.)
So, looking at my Dimension Usage tab, the Employee dimension is referenced by both the Productive Time and Call Summary measure groups but then the Rosters dimension only references the Productive Time and the Call Date dimension only references the Call Summary. Is that the problem ? Does a calculated member, that uses measures from 2 different measure groups, need to be scoped only to a dimension that also references both measure groups?
I have tried to write 2 separate scope statements and scope at different levels but cant seem to get it to work.
Thanks again
Garry
Hi
Okay, the only way I could get it to work was to define the Dimension Usage like this:
http://imgur.com/sQi24eY
Issues i have with this are:
1. My Roster dimension is now related to my Call Summary measure group via my Call Dates dimension. Doesnt seem correct to be doing that.
2. My Date dimension that is role playing as a Call Date dimension is now linked to my Roster Start Date. I dont want a seperate date dimension for my Roster Start Date and if i did, i would add the Date dimension as another role playing dimension of ‘Roster Start Date’….but it seems i would not be able to do this and have my calculation work.
There is something I am not understanding here…..feels like I am starting to hack this together instead of doing it properly.
Many thanks
Garry
A reference dimension shouldn’t be necessary here. What were the relationships before?
The relationships circled in red is what I have newly defined to get the calculation to work.
When i included the below measure in my query execution time jumped by 2 hrs. How to rewrite the below measure.
MEMBER [Measures].[Med] AS MEDIAN( filter ( NONEMPTY (
[Dimension].[Key].CURRENTMEMBER.CHILDREN
,[Measures].[Cycledays])
,[Measures].[Count]=1)
,[Measures].[Cycledays])
It’s hard to say. My guess is that it’s the Median() function that is making things slow, but there’s probably no way of replacing it.
Hell people, i have a problem that needs very urgent help.
I am trying to write a query
with member [Measure].[Distinctcount]
as
distinctcount({[Order Created Date].[Order Created Day Name Of Week],[Measures].[Ordered Qty]}
)
select
non empty
{
[Order Created Date].[Order Created Weekday Weekend]
, [Measure].[Distinctcount]
}
on columns
,
non empty
{
[Site].[Site Hierarchy].[Site Name].allmembers
}
on rows
from (
select
{
[Order Created Date].[Order Created Week Hierarchy].[Order Created Date].&[20151101]
–:[Order Created Date].[Order Created Week Hierarchy].[Order Created Date].&[20151130]
}
on columns
from [The Cube]
)
but all i continue to get is
Executing the query …
The Distinctcount calculated member cannot be created because its parent is at the lowest level in the Title hierarchy.
Execution complete
can anybody help me with this?
I think there’s a typo in your code: you have written [Measure].[Distinctcount] and you probably should have written [Measures].[Distinctcount] if you wanted to create a calculated measure.
Hi Chris,
I am struggling with scoped measures.Measure ‘X’ gets its value from different fact tables.Lets consider Time (Fiscal Week, Month)dimension and Channel dimension. For different combination of attributes in these two dimensions X will get its value from different tables as follows:
Week + Channel – gets from table FactTrafficByWeekChannel
Week – gets from table FactTrafficByWeek
Month + Channel – gets from table FactTrafficByMonthChannel
Month – gets from table FactTrafficByMonth
Note: Weeks values cannot be rolled up month as it is visitors count measured for different periods(Week, month, quarter)
To achieve this I added these fact to cube and created a calculated measure and scope scripts to overwrite the scope. Following is the scope script statement:
CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[Y]
AS (0),
FORMAT_STRING = “Standard”,
VISIBLE = 1;
Scope
([Measures].[Y],[Dim Time].[Fiscal Week].[Fiscal Week].Members
) ;
This = [Measures].[X – Vw Fact Total Weekly Traffic];
End Scope ;
Scope
([Measures].[Y],[Dim Time].[Fiscal Week].[Fiscal Week].Members,
[Dim Campaign].[Channel].[Channel].Members
) ;
This = [Measures].[X – Vw Fact Total Weekly Traffic By Channel];
End Scope ;
Scope
([Measures].[Y],[Dim Time].[Fiscal Month].[Fiscal Month].Members
) ;
This = [Measures].[X – Vw Fact Monthly Traffic];
End Scope ;
Scope
([Measures].[Y],[Dim Time].[Fiscal Month].[Fiscal Month].Members,
[Dim Channel].[Channel].[Channel].Members
) ;
This = [Measures].[X – Vw Fact Monthly Traffic By Channel];
End Scope ;
Above code works fine when corresponding dimension attributes are dragged in browsing pane but do not work when added to filter pane.Problem is I believe there is no way to know the attributes selected in filter pane in multidimensional cube. But I guess DAX function ‘IsFiltered’ identifies whether an attribute in being filtered or not. Is there an equivalent function in MDX ? Or, is there any other way of doing this ?
Can anybody help me with this?
Scope statements should work with the filter pane – my guess is that you have got the order of your assignments wrong and the later assignments are overwriting the earlier assignments. You need to have the scope statements that make the lower assignments earlier in the script; for example the assignment for Week+Channel should come before the assignment for Week. This other blog post might help you too: http://blog.crossjoin.co.uk/2013/05/23/using-scoped-assignments-to-show-calendar-and-financial-year-to-date-values-in-the-same-calculated-measure/
Hi Chris,
I’m attempting to replace measure values in the fourth level of a parent-child hierarchy via a case statement, but the subtotal of the third level is displaying the total as if the values were not being replaced even though the subtotal of the fourth level aggregates as expected. Any idea as to what I’m missing? Thanks so much for your help!
SCOPE
([Measures].[Balance Sheet]);
Scope([Master Date].[Calendar].MEMBERS);
SCOPE
[Statements].[Statements].[Level Four].Members;
Scope [Statements].[Statements].Members;
this =
CASE
WHEN
(NOT
[Statements].[Statements].CurrentMember
IS
[Statements].[Statements].[Level Four].[Net profit/(loss)])
AND
(NOT
[Statements].[Statements].CurrentMember
IS
[Statements].[Statements].[Level Four].[Retained Earnings])
THEN
[measures].[ending balance]
WHEN
[Statements].[Statements].[Level Four].[Net profit/(loss)]
IS
[Statements].[Statements].[Level Four].[Net profit/(loss)]
AND
(NOT
[Statements].[Statements].CurrentMember
IS
[Statements].[Statements].[Level Four].[Retained earnings])
THEN
[Measures].[Net Change]
WHEN
[Statements].[Statements].[Level Four].[Retained earnings]
IS
[Statements].[Statements].[Level Four].[Retained earnings]
AND
(NOT
[Statements].[Statements].CurrentMember
IS
[Statements].[Statements].[Level Four].[Net profit/(loss)])
THEN
[measures].[beginning balance]
END;
END SCOPE;
END SCOPE;
End scope;
End Scope;
Is Balance Sheet a calculated measure? If so, that would explain why it isn’t aggregating up. Otherwise, this SCOPE statement looks suspicious:
SCOPE
[Statements].[Statements].[Level Four].Members;
Scope [Statements].[Statements].Members;
Is that what you actually have? There are brackets missing here for a start, so I would be surprised if this worked.
I’m also noticing that the case statement order determines the overall subtotal of level 3. For example, if I put the case for the beginning balance measure first, the subtotal equals the value of the beginning balance cells at that level. Could it be something wrong with the case statement?
I must have not replied directly to your comment and created a new comment below. Sorry about that.
Here is my original reply to your first comment:
Sorry about that. I do have the brackets around the scope statements. MDX Studio stripped them out when I formatted the query. The aggregations work fine going up to the first level. The subtotal is only off on the third level subtotal where i’m replacing the measures.
Balance sheet is a ‘dummy’ measure as per your example above. However, Beginning balance and Ending balance are calculated measures. Net change is a real measure. Thanks for the help!
I think you need to remove the scope on [Statements].[Statements].Members and just scope on [Measures].[Balance Sheet], [Master Date].[Calendar].MEMBERS and [Statements].[Statements].[Level Four].Members
Thanks Chris. That’s what I originally had my scope assignments set to but the calculation stopped working once I associated the parent child hierarchy to the fact table via a many-to-many relationship since i wanted to add another parent-child hierarchy to the same dimension (income statement). I ended up creating two views on top of the physical hierarchy dimension table and relating those to the fact table individually via foreign keys and the scoped assignments evaluated correctly again. Are you aware of issues with scope assignments on a referenced referenced measure via a bridge table? Thanks again for your help.
Ah, yes I think the m2m relationship will change things – the presence of a m2m relationship will stop a measure being additive.
Sorry about that. I do have the brackets around the scope statements. MDX Studio stripped them out when I formatted the query. The aggregations work fine going up to the first level. The subtotal is only off on the third level subtotal where i’m replacing the measures.
Balance sheet is a ‘dummy’ measure as per your example above. However, Beginning balance and Ending balance are calculated measures. Net change is a real measure. Thanks for the help!
Hi Chriss,
I’m thinking to create Measure, purpose is to utilize in powerpivot to make the filter.Suppose I’ve plan year and operation as filter, when I selected “2015 “(Plan Year) and “Division of External Relation”(Operation). The below attributes(Account ID ,AccountName, FromYear, ToYear and Countof MSRP), which are filtered out like based plan year and operation. But, My records like below for sample
AccountID AccountName FromYear ToYear COuntof MSRP
1234 abc 1992 2007 124 –>This record has to remove in powerpivot
1234 cde 2007 4000 124
Regards,
Ameer
I’m sorry, I don’t understand what you want to do here properly.
Chris,
In source side, a two additional attributes(ex: FromYear and Toyear) were added in same dimension(ex: Account Dimension). I added two attributes at existing cube design. While I’m browsing the cube at powerpivot, based on the selection(Ex: PlanYear = 2015) the below structure will replicate
AccountID AccountName FromYear ToYear COuntof MSRP
1234 abc 1992 2007 124 –>This record has to remove in powerpivot
1234 cde 2007 4000 124
I just wanted to remove the “1234 abc 1992 2007 124 ” record when i was selected 2015 as planyear.
Please suggest on this requirement.
I’m still confused, sorry – are we talking about SSAS here or Power Pivot? Or are you consuming data from SSAS Multidimensional in Power Pivot? If so, why?
Chris, this seems to be the best method for Same Store Sales calculations. I’ve seen some other approaches suggested but this is so much simpler. I’m scoping at the Store member level (excluding the All) and using Lag to check if data exists in both month and prior month (and year and prior year) on the data hierarchy for instance and not setting the measures to Null. Thank you!
Chris, can you comment on whether the following is a good tack to use? I added calcs to the Adventureworks cube after adding 4 additional Named Calculations (named per Scope statements below) to the DSV Reseller fact (because this is a named query I had to add to each partition, but for Table/Views I believe it only needs to be added to the DSV and not the partitions)
Will do Prior Period or Prior Year Parallel Period at all levels in the Ship Date Calendar and Fiscal hierarchies for the resellers:
——- cube calcs
— Date Hierarchy – handy indicators / measure “bits” for reference in calculations; What level of the Calendar or Fiscal hierarchies are we at (Year, Half, Quarter, Month)?
CREATE MEMBER CURRENTCUBE.[Measures].[Calendar Year Level]
AS IIF([Ship Date].[Calendar].Currentmember.Level IS [Ship Date].[Calendar].[Calendar Year], 1, 0),
VISIBLE = 0 ;
CREATE MEMBER CURRENTCUBE.[Measures].[Calendar Semester Level]
AS IIF([Ship Date].[Calendar].Currentmember.Level IS [Ship Date].[Calendar].[Calendar Semester], 1, 0), VISIBLE = 0 ;
CREATE MEMBER CURRENTCUBE.[Measures].[Calendar Quarter Level]
AS IIF([Ship Date].[Calendar].Currentmember.Level IS [Ship Date].[Calendar].[Calendar Quarter], 1, 0), VISIBLE = 0;
CREATE MEMBER CURRENTCUBE.[Measures].[Calendar Month Level]
AS IIF([Ship Date].[Calendar].Currentmember.Level IS [Ship Date].[Calendar].[Month], 1, 0),
VISIBLE = 0;
CREATE MEMBER CURRENTCUBE.[Measures].[Fiscal Year Level]
AS IIF([Ship Date].[Fiscal].Currentmember.Level IS [Ship Date].[Fiscal].[Fiscal Year], 1, 0),
VISIBLE = 0;
CREATE MEMBER CURRENTCUBE.[Measures].[Fiscal Semester Level]
AS IIF([Ship Date].[Fiscal].Currentmember.Level IS [Ship Date].[Fiscal].[Fiscal Semester], 1, 0),
VISIBLE = 0;
CREATE MEMBER CURRENTCUBE.[Measures].[Fiscal Quarter Level]
AS IIF([Ship Date].[Fiscal].Currentmember.Level IS [Ship Date].[Fiscal].[Fiscal Quarter], 1, 0),
VISIBLE = 0;
CREATE MEMBER CURRENTCUBE.[Measures].[Fiscal Month Level]
AS IIF([Ship Date].[Fiscal].Currentmember.Level IS [Ship Date].[Fiscal].[Month], 1, 0), VISIBLE = 0;
— end hierarchy indicator flags
— Straight Date Hierarchy calcs below for Prior Year and Prior Period order quantities
— Prior Year ——————–
CREATE MEMBER CURRENTCUBE.[Measures].[Reseller Order Quantity – Prior Year*]
AS CASE WHEN [Measures].[Calendar Year Level] = 1 — at Year Level in Calendar Hierarchy
THEN ([Ship Date].[Calendar].Currentmember.lag(1) ,[Measures].[Reseller Order Quantity])
WHEN [Measures].[Calendar Semester Level] = 1 — at Semester Level in Calendar Hierarchy
THEN ([Ship Date].[Calendar].Currentmember.lag(2) ,[Measures].[Reseller Order Quantity])
WHEN [Measures].[Calendar Quarter Level] = 1 — at Quarter Level in Calendar Hierarchy
THEN ([Ship Date].[Calendar].Currentmember.lag(4) ,[Measures].[Reseller Order Quantity])
WHEN [Measures].[Calendar Month Level] = 1 — at Quarter Level in Calendar Hierarchy
THEN ([Ship Date].[Calendar].Currentmember.lag(12) ,[Measures].[Reseller Order Quantity])
WHEN [Measures].[Fiscal Year Level] = 1 — at Year Level in Fiscal Hierarchy
THEN ([Ship Date].[Fiscal].Currentmember.lag(1) ,[Measures].[Reseller Order Quantity])
WHEN [Measures].[Fiscal Semester Level] = 1 — at Semester Level in Fiscal Hierarchy
THEN ([Ship Date].[Fiscal].Currentmember.lag(2) ,[Measures].[Reseller Order Quantity])
WHEN [Measures].[Fiscal Quarter Level] = 1 — at Quarter Level in Calendar Hierarchy
THEN ([Ship Date].[Fiscal].Currentmember.lag(4) ,[Measures].[Reseller Order Quantity])
WHEN [Measures].[Fiscal Month Level] — at Month Level in Fiscal Hierarchy
THEN ([Ship Date].[Fiscal].Currentmember.lag(12) ,[Measures].[Reseller Order Quantity])
ELSE
NULL
END,
FORMAT_STRING = “#,##0;(#,##0)”, VISIBLE = 1 , DISPLAY_FOLDER = ‘Prior Periods’ , ASSOCIATED_MEASURE_GROUP = ‘Reseller Sales’;
— Prior Period ——————-
CREATE MEMBER CURRENTCUBE.[Measures].[Reseller Order Quantity – Prior Period*]
AS CASE WHEN [Measures].[Calendar Year Level] = 1 OR [Measures].[Calendar Semester Level] = 1 OR [Measures].[Calendar Quarter Level] = 1 OR [Measures].[Calendar Month Level] = 1– in Calendar Hierarchy
THEN ([Ship Date].[Calendar].Currentmember.lag(1) ,[Measures].[Reseller Order Quantity])
WHEN [Measures].[Fiscal Year Level] = 1 OR [Fiscal].[Calendar Semester Level] = 1 OR [Measures].[Fiscal Quarter Level] = 1 OR [Measures].[Fiscal Month Level] = 1– in Fiscal Hierarchy
THEN ([Ship Date].[Fiscal].Currentmember.lag(1) ,[Measures].[Reseller Order Quantity])
ELSE NULL
END,
FORMAT_STRING = “#,##0;(#,##0)”,
VISIBLE = 1 , DISPLAY_FOLDER = ‘Prior Periods’ , ASSOCIATED_MEASURE_GROUP = ‘Reseller Sales’;
— end Straight up “Prior” calcs ———
— Same Store/Reseller logic
— The scoped Measures below are Named Calculations in the DSV, added to the Reseller Sales Measure Group. Scoping to each Reseller level (MUST exclude the All level in the hierarchy) results
in those calculations totaling up correctly (we only include in totals if sales were in both periods).
SCOPE([Measures].[Reseller Order Quantity – Same Store Sales – Year]);
SCOPE([Reseller].[Reseller].[Reseller].MEMBERS);
THIS = IIF([Measures].[Reseller Order Quantity – Prior Year*] 0 AND [Measures].[Reseller Order Quantity] 0, [Measures].[Reseller Order Quantity], null);
END SCOPE;
END SCOPE;
SCOPE([Measures].[Reseller Order Quantity – Same Store Sales – Year Prior]);
SCOPE([Reseller].[Reseller].[Reseller].MEMBERS);
THIS = IIF([Measures].[Reseller Order Quantity – Prior Year*] 0 AND [Measures].[Reseller Order Quantity] 0, [Measures].[Reseller Order Quantity – Prior Year*], null);
END SCOPE;
END SCOPE;
SCOPE([Measures].[Reseller Order Quantity – Same Store Sales – Period]);
SCOPE([Reseller].[Reseller].[Reseller].MEMBERS);
THIS = IIF([Measures].[Reseller Order Quantity – Prior Period*] 0 AND [Measures].[Reseller Order Quantity] 0, [Measures].[Reseller Order Quantity], null);
END SCOPE;
END SCOPE;
SCOPE([Measures].[Reseller Order Quantity – Same Store Sales – Period Prior]);
SCOPE([Reseller].[Reseller].[Reseller].MEMBERS);
THIS = IIF([Measures].[Reseller Order Quantity – Prior Period*] 0 AND [Measures].[Reseller Order Quantity] 0, [Measures].[Reseller Order Quantity – Prior Period*], null);
END SCOPE;
END SCOPE;
Hi Eric, I can’t say I’ve done more than take a quick look, but this seems reasonable. You can probably simplify it a lot more though by replacing all of those CASE statements to check what level you’re at with more SCOPE statements to handle the same logic.
Hi Chis,
I have a problem regarding grand total.In my scope i am not using any measures which was already scoped and without measures its not giving correct grand total.Can you please suggest any other approach to achieve.
SCOPE ([Measures].[Amount], [Scenario].[Scenario].[Actual]);
THIS = [Measures].[Amount];
END SCOPE;
SCOPE ([Measures].[Amount], [Scenario].[Scenario].[Forecast]);
THIS = [Measures].[Forecast Amount];
END SCOPE;
SCOPE ([Scenario].[Scenario].[Forecast]);
THIS =(CASE WHEN ([Scenario].[Scenario].[Forecast] is null or [Scenario].[Scenario].[Forecast] =0) –or [Scenario].[Scenario].[Forecast] =0)
THEN ([Scenario].[Scenario].[Actual]) ELSE ( [Scenario].[Scenario].[Forecast]) END);
END SCOPE;
Is [Amount] a calculated measure or a regular measure?
Hi Chris,
It’s a calculated measures.
Regards,
Navneet
If it’s a calculated measure, then that explains why you don’t see the totals you are expecting. As the blog post explains, you will need to create a regular measure for this to work.
Hi Chris,
Thanks for responses.Unfortunately we have multiple calculated measures which are associated with dimension ‘scienario’ which I am using with ‘scope’ definition.
Please let me know if any way to achieve grand total with scope definition.
No, you can’t do it with calculated measures. As the blog post says, you have to create regular measures instead of calculated measures and use scope statements on them.
Hello Chris,
Thanks a lot for your Blog, very helpful,
I have the same problem with a cube that i’m running and i cant figure how to resolve it.
My cube consists of several dimensions and a single Measure. The goal is to calculate the selling price, which is simply: Unit Price * Number of Units. The unit price and the number of units are attributes of a dimension, and their values can be retrieved directly from the database.
The selling price is calculated directly in the cube via MDX :
SCOPE (
[Entity].[Entities].[Country].members,
[Period].[Year].[Month].members,
[Products].[Products].[Product].members,
[Channel].[Hierarchy].[Customer].members);
[Accounts].[Account].[Selling Price] =
[Accounts].[Account].[Unit Price]
* [Accounts].[Account].[Number of Units];
END SCOPE;
Know that all the Scope members are hierarchies. And this happens only when a Product contains 2 Sub Products. Otherwise it work perfectly.
For example :
Country : USA
Year : 2017
Month : August
Customer : SampleCustomer
| Unit Price | Number of Units | Selling Price |
Product A
SubProduct A1 | 2 | 10 | 20 |
SubProduct A2 | 3 | 10 | 30 |
Grand Total | 5 | 20 | 100 |
Which is not correct, the grand total of Selling Price must be 50, not 100.
If you have any idea how to figure this out, it could be very helpful.
Kind Regards
Do you have a Sub Product attribute on your dimension? If so, you should be scoping at that level and not Product.
Hi Chris,
I have an below MDX Scope assignment I am having issues with.
In the AdventureWorks Cube,
Sales Quota measure is at the Quarter level and to allocate the values at the month level, parent member is divided by 3.
What if the Current member is at the day level and parent member is at month level & I want to get the equal allocation at the day level.
Here I am dividing parent member by 30 but it is not giving the correct results. Is there any ways the month values are allocated to the day level correctly( for example January has 31 days and February has 28 days)
SCOPE ([Measures].[Sales Quota]);
SCOPE ([Posting Date].[Year].&[2010],
[Posting Date].[Date YQMD].[Day].Members);
THIS = [Posting Date].[Date YQMD].CurrentMember.Parent/30;
Format_String(THIS)=’#,#0.00′;
END SCOPE;
END SCOPE;
Looking forward for your positive reply.
Thanks,
Bhavesh
Hi Bhavesh, it sounds like you need a measure that counts the number of days in the current month. Why not build a measure group from your Date dimension table with a simple count measure? Assuming this measure is called [Count of Days], you can then use a tuple like (Measures.[Count Of Days], [Posting Date].[Date YQMD].CurrentMember.Parent) to get the number of days in the current month at the day level.
Thank you very much. You are amazing & great. This has solved my problem.
Hi Chris, I want to do exactly this but everywhere I look for a solution on leaf level aggregation it’s always scoped for a specific set of attribute hierarchys/granularity, for example in this case it’s for date and product attribute hierarchys. What I really want to do is say for a particular measure group, for a calculated measure (based on a combination of measure group measures) I want the result to be aggregated from the leaf level always, irrespective of what dimensional attributes I slice or filter by. As long as the dimension is related to the measure group through dimension usage, that measure will be aggregated from the leaf level always. Do I have to manually scope in every dimension attribute to do this or is there an easier way? Doing it in the DSV is not an option so I want to do this with a calculated measure. Basically I want to achieve the same thing as a MeasureExpression (for a “real” measure in a measure group based on a dsv Fact attribute) but with a calculated measure using MDX. It seems like it should be so simple but I can’t seem to find anything that works more elegantly than simply scoping for every dimension attribute I wish to slice by. Thanks in advance.
Sorry when I said doing it in the DSV is not an option I meant the calculation itself, creating a null value like you have above is no problem, just the calculation needs to happen with MDX.
Good question and a perfectly valid requirement. You do have to scope on the key attribute of every dimension related to the measure group which is a bit of a pain, I know. There is the Leaves() function which I believe was intended for this exact scenario: https://docs.microsoft.com/en-us/sql/mdx/leaves-mdx?view=sql-analysis-services-2017 It doesn’t scope on the members of the lowest level on the key attribute though – it scopes on the leaves of every attribute in the dimension, which isn’t quite the same thing, but it might still work so I would need to test it. I think this would be a good subject for a blog post…
Chris, thanks for getting back to me so quickly! I know what you are talking about and I saw this example:
SCOPE(LEAVES(MyMeasureGroup), [Measures].[MyRegularMeasure]);
This = [Measures].[MyRegularMeasure]/2;
END SCOPE;
From this forum post https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a97abba2-7795-4110-bad5-ecc1f98aef91/how-to-avoid-leaflevel-calculations?forum=sqlanalysisservices
I thought this was the answer to my problems, as it seems to imply that you can pass the measure group to the leaves function, specify your measure you want to leaf level aggregate and off you go. unfortunately I couldn’t get it to work it wants a dimension passed to it which is akin to having to scope key attributes individually.
The answer to this post under the heading “Position on Measure Group Leaves” seems to imply the same thing https://social.msdn.microsoft.com/Forums/sqlserver/en-US/05362eb3-6535-4327-a380-9c50c28d3b07/mdx-leaves-function?forum=sqlanalysisservices
It says the one way of using the Leaves() function is to Quote “positions on the granularity of the measure group by all of the dimensions related to that measure group” to me that sounds like it’s meant to do what I am trying to do, but maybe I am misunderstanding it…
Finally, from the link you have given me MS seems to mention nothing of the sort it insists on a dimension being passed, if it is absent it brings back all leaf members of the cube which is not useful either.
So I guess it would be good to know if this function was in fact intended to do what I need to do, cause from what I tried I couldn’t get it to work, or any other approach for that matter.
thanks again,
Des
Sorry just to clarify when I said is “akin to having to spoke key attributes individually” not quite, less work, instead of every key attribute you would have to cover every dimension related to the measure group–>Measure in question. thanks Des.
You should be careful reading posts from back in 2006: no-one (including me, and I wrote that section quoted from the “Spofford book”) really understood this stuff back then, and anyway things have changed since then 🙂
Anyway, what I think should work is something like this:
SCOPE(MEASURES.MYMEASURE);
SCOPE(LEAVES());
THIS = 123;
END SCOPE;
END SCOPE;
What should happen here is that LEAVES(), when used in a nested scope statement, picks up the current context and returns a subcube which is all of the leaf members on all the hierarchies of all the dimensions relating to the measures in the current scope. If you have measures from different measure groups with different granularities you’ll get an error. But as I said, I’m not 100% sure of this and I’ll try to do some testing of my own. I would be interested to know if it works for you.
Thanks for your insight Chris, I’ll give it a crack and let you know.
Des.
Hi Chirs I tried it but it returned blank cells (null values). There is no errors and the script deploys without issue, it just doesn’t return anything.
The base measure Y is a measure derived directly from a fact table in the dsv the aggregation type of that measure is defined as count.
CREATE MEMBER CURRENTCUBE.[Measures].[RatioMeasure]
AS IIF([Measures].[BaseMeasureY] = 0,NULL,
[CalculatedMeasureX]/[Measures].[BaseMeasureY]),
FORMAT_STRING = ‘$#,##0.00’,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘TestMeasureGroup’ ;
CREATE MEMBER CURRENTCUBE.[Measures].[TestLeafRollUp] AS
null,
FORMAT_STRING = ‘$#,##0.00’,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘TestMeasureGroup’;
SCOPE([Measures].[TestLeafRollUp]);
SCOPE(LEAVES());
THIS =[Measures].[RatioMeasure] * 28;
END SCOPE;
END SCOPE;
thanks,
Des
Hi Des, this is probably because you are scoping on a calculated measure and not a real measure. With this code I would only expect values to be visible if you select members at the lowest granularity of every single dimension related to the measure group. Can you try creating a real measure and scoping on that, as shown in the blog post?
Ok no problem I’ll try that and let you know.
Des
I haven’t forgotten about this some things have come up is all I will post back shortly. thanks Des.
Hi Chris,
I tried this as you suggested and created the real measure I left the default calculation as sum as you have done above. The definitions of the measures in play remain the same as per my previous post above May 28, 2018 at 1:14 am the only difference is for the sake of simplicity I have removed the multiplication that was previously included in the scoped statement:
Original above:
SCOPE([Measures].[TestLeafRollUp]);
SCOPE(LEAVES());
THIS =[Measures].[RatioMeasure] * 28;
END SCOPE;
END SCOPE;
What we have now:
SCOPE([Measures].[TestLeafRollUp]);
SCOPE(LEAVES());
THIS =[Measures].[RatioMeasure] ;
END SCOPE;
END SCOPE;
It sort of works. I have pasted in a result set here which if you paste into excel renders sufficiently well to demonstrate the issue. The results:
Row Labels CalculatedMeasureX BaseMeasureY RatioMeasure TestLeafRollUp
2007 2,281,774,424.04 716330 3185.367671 533,414,514.85
2007 Q1 575,904,055.55 191693 3004.304046 136,138,635.63
2007 Q2 604,760,890.82 185313 3263.456373 131,882,677.61
2007 Q3 561,018,668.94 178140 3149.313287 127,572,180.78
2007 Q4 540,090,808.74 161184 3350.771843 137,821,020.83
2008 1,979,550,064.83 672201 2944.878191 538,112,193.68
2008 Q1 480,457,401.95 149097 3222.448486 126,041,211.49
2008 Jan 175,320,715.13 55357 3167.092059 43,483,264.36
2008-01-02 8,906,920.31 3330 2674.750845 2,096,509.58
2008-01-03 10,950,645.41 2591 4226.416598 2,657,869.95
2008-01-04 9,012,324.60 1896 4753.335759 2,921,650.37
2008-01-07 6,971,307.52 3146 2215.927375 1,760,399.25
2008-01-08 10,964,000.99 3122 3511.851695 2,327,008.34
2008-01-09 12,802,115.98 3511 3646.287662 3,034,452.83
2008-01-10 3,583,819.61 891 4022.244229 1,401,291.38
2008-01-11 11,803,779.91 2854 4135.872429 3,096,064.10
2008-01-14 6,503,778.29 3702 1756.82828 1,399,593.76
2008-01-15 5,880,063.39 2161 2720.991852 1,498,296.23
2008-01-16 3,850,723.60 3481 1106.211894 1,012,678.43
2008-01-17 2,754,610.75 1013 2719.260368 978,686.95
2008-01-18 19,457,001.73 4424 4398.056449 4,578,542.26
2008-01-21 6,326,574.67 1671 3786.100937 1,502,722.02
2008-01-22 7,871,661.81 3473 2266.530899 2,021,444.69
2008-01-23 14,152,045.70 3113 4546.111694 2,526,304.80
2008-01-24 3,079,359.80 863 3568.20371 1,085,377.11
2008-01-25 10,897,517.64 3411 3194.816077 2,615,161.41
2008-01-29 5,351,959.38 2624 2039.618665 1,717,787.26
2008-01-30 9,676,018.02 3163 3059.126786 2,201,077.74
2008-01-31 4,524,486.00 917 4934.008729 1,050,345.91
2008 Feb 153,482,356.97 47174 3253.537054 42,663,602.14
2008 Mar 151,654,329.85 46566 3256.760938 39,894,344.99
2008 Q2 464,490,742.49 156912 2960.198981 125,720,078.00
2008 Q3 518,665,625.55 181610 2855.930981 139,240,890.42
2008 Q4 515,936,294.84 184582 2795.160389 147,110,013.77
Grand Total 4,261,324,488.88 1388531 3068.944438 1,071,526,708.53
There aggregation of the TestLeafRollUp Measure occurs as would be expected. The problem is that the leaf level figure is not correct to start with and I am not sure how the figure is being calculated.
To clarify when I say correct, I would expect Ratio Measure and TestLeafRollUp measure values to be the same at the leaf/most granular level which in this case is day. I would expect the “traditional” aggregated measure “Ratio Measure” figure for January 2008 to be as it is, 3167.092 (175,320,715.13/55357) and for the leaf level aggregated measure TestLeafRollUp I would expect to see a total figure for Jan 2008 of 69282.55, equal to all the days in the month summed. everything should roll up from there in the same manner.
The values are however very different, the incorrect leaf level figures are being rolled up and aggregated correctly but because the figures themselves are not correct the desired result is not achieved. It’s almost as if at the leaf level the implicit calendar hierarchy filters aren’t being applied to the values. Further investigation of the actual query that is being issued to the engine might shed further light on this but at this stage it doesn’t seem to do what I want.
Regards,
Des
Just to be clear, you only have one dimension- the Date dimension- that has a relationship with this measure group, right?
No there are other dimensions.
In the original post was trying to find a way to do this without explicitly having to scope in every one of them.
Yes, so I think maybe you are misunderstanding what scoping on each one of them means. In your first post today your query only shows data at the day level, but the scope statement you have using the LEAVES() function scopes at the lowest granularity of all the dimensions in the measure group, not just the Date dimension. Your test query will only show the same values for RatioMeasure and TestLeafRollup when you have a query that shows data not just at the Date granularity but Date *and* the lowest granularity of all the other dimensions as well.
I just realized that the data doesn’t paste into excel ok after you make the post…….. If you want I can email them too you.
thanks Des.
OK thanks Chris,
That makes sense because days can have multiple values that when combined with other lowest grain members of other dimensions, they will then aggregate. I don’t want to have to bring in all the other dimension lowest grain attributes though. I just want the two measures to behave exactly the same, so in this case same values at the day level. The only difference is in the way in which they aggregate. Is it possible to achieve?
thanks,
Des
Yes, to do this you just need to scope at the lowest granularity of the Date dimension (which will be your Date attribute), and not use the LEAVES() function. So your scope statement will be something like:
SCOPE([Date].[Date].[Date].MEMBERS)
Ok thanks for that Chris I tried this during testing and if I’m using the date dimension attribute members [Date].[Date].[Date].MEMBERS only in the query no problem, if for example I want include region on rows as well while preserving the same behavior for the measure I would need to add
SCOPE [Region].[RegionName].[RegionName].MEMBERS in the scope statement which after testing also gives the desired aggregation result.
If I do this for every potential attribute I wish to use in any subsequent queries where I want the TestLeafRollUp measure to aggregate as outlined above it works, it’s just cumbersome to add them all individually, I was wondering if there was any way around that. I guess then it has to be done this way.
thanks again for your help with this.
Des.
Sorry, I know it feels like we’re going round in circles a bit. However the reason I made my last comment is that if you do scope on all the attributes separately (eg if there were only two dimensions, Date and Region, and you scoped on [Date].[Date].[Date].MEMBERS and [Region].[RegionName].[RegionName].MEMBERS), you would not get the results you said you were expecting in your last post either with TestLeafRollup and RatioMeasure returning the same values when you run a query that only looks at Date values.
OK thanks for you help with this Chris, I managed to put the pieces together here and get the results I required. thanks Des.
Hi Chris,
Good day.
I am new to this software (Jet Analytics) that I am using and i think it deals with MDX expression for some custom choices of setup when a designer is creating measures.
In my case, i have created a standard measure which pulls data from a source column in a data warehouse object. The measure works and display values when report is generated, however, when i got to check the grand total of the said measure. it is different with what the excel is calculating. Is there any way on how can i use MDX expression to just count what the pivot table values is showing in the report?
Tank you in advance
The details of the measure are as follows:
Sales Channel (Dimension)
docket 01= 2
docket 02= 2
docket 03= 2
docket 04= 5
total docket should be 11.
Yes, it sounds like the technique described in this post will do what you want to do.
HI Chris, IGood Morning.
In my SSAS cube i have move type dimension and mesaures associated with it. But when i pull the calculated measured “ODRAYExpense” against the MoveType dimension it is repeating values instead of showing the value on MoveType “ODRAY”.. Is there any way to fix
MoveType Expense ODRAYExpense
DDRAY 876 27
ODRAY 27 27
Very well written and clear Chris! Would this approach work to fix my performance issue? Not exactly sure how I would approach this?
https://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/6bbe0a56-4e2a-4b2e-9614-b5d0a99d1957/
Cory Bonallo
HI Chris.
Thanks for this article.
I followed this approach for a requirement I have, similar to what you presented here.
It works fine as long as a Product member is selected. When the product All member is selected, it does not work.
is there a way to make the All member of the Product just return the sum of all the Individual members?
I also noticed that you said the Scope statements were formulated to avoid the All members. so I understand why it would not work if my user is selecting the All member. But I am wondering if there is a Scope formulation that would sum all the individual calculated members, should the user select the All members in the spreadsheet.
Please kindly advise me.
Thank you.
This approach should do what you’re describing – if it doesn’t you may have made a mistake in your MDX somewhere.
You were correct, there was an error in my MDX. I corrected and its working as expected.
Thanks again.
Hi Chris,
I was searching for a statement like yours:
“This is where scoped assignments come in. If you make a scoped assignment to a real measure, as opposed to a calculated measure, then the value of the assignment will aggregate up outside of the original scope. So, in this case, since you want the calculation to take place at the Date and Product granularity, if you scope on a real measure at that granularity the result of the calculation will aggregate up automatically.”
So…Thank you 🙂 I wonder about this topic a long time and did not find a solution on any microsoft websites.
Hi Chris,
maybe this is the wrong topic, for a (in my mind) similar problem, which I can’t figure out:
How do I get the [measures].[hours_workable] behave correctly in the following situation?
There is 1 fact table with three dimensions:
fct_hours
dim_date, dim_employee, dim_department
date employee department hours_workable assigned hours_assigned
2020-04-08 John sales 8 75% 6
2020-04-08 John marketing 8 25% 2
2020-04-09 John sales 8 75% 6
2020-04-09 John marketing 8 25% 2
2020-04-08 Pete sales 8 75% 6
2020-04-08 Pete marketing 8 25% 2
2020-04-08 Pete purchase 8 100% 8
2020-04-09 Pete sales 8 75% 6
2020-04-09 Pete marketing 8 25% 2
2020-04-09 Pete purchase 8 100% 8
2020-04-09 Mary marketing 8 25% 2
2020-04-09 Mary purchase 8 50% 4
There is a granularity issue here, an employee is scheduled to work for 8 hours per date. We could have decided to use 2 different fact tables, but there’s only one.
Also, for John, all of his 8 hours per day have been assigned, Pete, however, has recently moved from the purchase department to sales & marketing as well, only his assigned hours weren’t properly cancelled in the IT systems, leaving him with twice as many assigned as hours. Mary is has been assigned 6 of her total 8 hours. (administration is a mess 🙂 )
when we query the facts per month per employee (leaving out department and aggregating the 2 dates), we get a result which doesn’t match expectations:
month employee hours_workable hours_assigned assigned%
2020-04 John 32 (should be 16) 16 (correct) 50% (should be 100%)
2020-04 Pete 48 (should be 16) 32 (correct) 66.67% (should be 200%)
2020-04 Mary 16 (should be 8) 6 (correct) 37.5% (should be 75%)
TOTAL 96 (should be 40) 54 (correct) 56.25% (should be 135%)
the assigned% is a calculated measure:
CREATE CALCULATED MEASURE [assigned%] as [hours_assigned] / [hours_workable];
So, there’s the issue, how do I get the [measures].[hours_workable] to behave correctly?
I’ve tried scoping, no luck 🙁
SCOPE([Measures].[assigned%]]);
SCOPE([dim_employee].[dim_employee].MEMBERS);
SCOPE([dim_department].[dim_department].MEMBERS);
THIS = AVG([Measures].[assigned%]);
END SCOPE;
END SCOPE;
END SCOPE;
Hi Chris,
I have a situation like you explained here. I have a calculated member that is fine and it is calculated cossing to fact tables but i have to cross it with another fact table (the third) and when doing this i obtain a different column total. Just like you explained.
I have created a null calculated member and when i set the scope in the mdx script visual .net says to me that i have a syntax error in the scope clause. The SCOPE word is red underliyed just like you have in the screenshot you posted here.
Second thing i need to now is how i have to set the escope..,,tell me if i dont understand it well…
SCOPE(Measures].[AUX]);
SCOPE([DIM of fact tabl1].[Atribute].[Hierarchy name].MEMBERS , [Dim of fact table 2].[Atribute].[Hierarchy name].MEMBERS, [Dim of fact table 3].[Atribute].[Hierarchy name].MEMBERS);
THIS = whatever;
END SCOPE;
Please, help me. I am stucked.
Thanks in advance
Hi again,
I will try t explain…
i did this:
First, the MDX script throws me a syntax error with SCOPE clause. It is red underlied, like your screenshot. Is is normal? Dont think so…
Second, i have this
SCOPE(Measures].[AUX]);
SCOPE([DIM Cor Elemento Comercial].[No].[Hierarchy].MEMBERS);
THIS = [Total Importe IVA INCLUIDO – Subv.]*[Measures].[Porc_ Fondo Garantia]/100);
END SCOPE;
END SCOPE;
Where [Total Importe IVA INCLUIDO – Subv.] is a calculated member with 2 measure groups A and B and both are related with the dimension [Dim Elemento Comercial]. The calculated memer is fine.
Then i have to multiply it to a member. This member is in another measure group that refers to the same dimension [Dim Elemento Comercial].
Any help with all this please, i am stucked…Please.
Thanks in advance
Hi again,
I have fixed the syntax error. Now i have this that returns the same bad total value, like it is not considering the scope so i am doing something wrong…
What i have understood is that i have to overvwrite the calculate member with the hierarchies of the involved dimensions. In my example, to the calculate i need to consider 3 dimensions and i use them in the scope clause but the result is the same. bad total.
SCOPE([Measures].[AUX]);
SCOPE([DIM Cor Elemento Comercial].[Hierarchy].MEMBERS);
SCOPE([FACT Cor Porc_ Participacion Elemento].[Hierarchy 1].MEMBERS);
SCOPE([VISESA Cor Lineas Ppto].[Hierarchy 1].MEMBERS);
THIS = [Total Importe IVA INCLUIDO – Subv.]*[Measures].[Porc_ Fondo Garantia]/100;
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
Thanks in advance
Hi Javier! My script works but I am also getting SCOPE keyword underlined with red. How did you fix it?
[Total Importe IVA INCLUIDO – Subv.] is composed of a lot of calculates (multiplication sums) and the result is fine. All this multiplication sums are from 2 measure groups that their dimensions are [FACT Cor Porc_ Participacion Elemento] and [VISESA Cor Lineas Ppto], who are related with the main dimension [DIM Cor Elemento Comercial]. And this [DIM Cor Elemento Comercial] has another measure group that has the member [Measures].[Porc_ Fondo Garantia].
Not for sure if i explain it well, i am sorry.
i got it. Thanks for the post!
I used calculation which is working fine until I used filters on Linecat which I used in scope. when I use filter on linecat attribute – subtotal and grand total are not matching. please suggest to resolve the total mismatch issue.
SCOPE ([Measures].[CFH_PREVIOUS_PB_NB]);
THIS = [Measures].[CFH_PREVIOUS];
SCOPE (FILTER([PREVIOUS PERIOD].[PERIOD].[PERIOD].Members,
[PREVIOUS PERIOD].[PERIOD].CurrentMember.Properties(“FIN PERIOD TYPE”) = “FACTUAL”)
);
SCOPE ([CF ATTRIBUTES 1].[LINECAT].&[NB New Business]);
THIS = null;
END SCOPE;
SCOPE ([CF ATTRIBUTES 1].[LINECAT].&[PB Previous Business]);
THIS =SUM({[CF ATTRIBUTES 1].[LINECAT].&[NB New Business], [CF ATTRIBUTES 1].[LINECAT].&[PB Previous Business]},[Measures].[CFH_PREVIOUS]);
END SCOPE;
END SCOPE;
END SCOPE;
Hi, Chris,
could you please help me with the following:
i have a multi-dimensional table, many measures in the columns and I cannot understand what to do to have calculation in rows:
final table looks like:
Country Actuals Target Forecast diff % YoY, %
Country1 30.0 30.0 30.0 0% 0%
Sales 50.0 50.0 45.0 -10% -10%
Expenses -20.0 -20.0 -15.0 -25% -25%
Country2 30.0 30.0 30.0 0% 0%
Sales 100.0 100.0 45.0 -55% -55%
Expenses -70.0 -70.0 -15.0 -79% -79%
I need the following:
Country Actuals Target Forecast diff, % YoY, %
Country1 30.00 30 30 0% 0%
Sales 50.00 50 45 -10% -10%
Expenses -20.00 -20 -15 -25% -25%
Margin 60% 60% 67%
Country2 30.00 30 30 0% 0%
Sales 100.00 100 45 -55% -55%
Expenses -70.00 -70 -15 -79% -79%
Margin 30% 30% 67%
is it possible to make it using standard powerpivot or create special set using MDX?
thank you very much!
Maria
Thank you very much Chris! I needed to calculate the minimal value at a lower aggregation level and then aggregate those values. The explanation in this article worked like a charm.