# Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

## Aggregating the Result of an MDX Calculation Using Scoped Assignments

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.

Written by Chris Webb

May 29, 2013 at 5:11 pm

### 79 Responses

1. 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

George Qiao

May 30, 2013 at 2:05 am

2. 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.

sri

May 30, 2013 at 6:21 am

• 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];

Chris Webb

May 30, 2013 at 6:45 am

• 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.

sri

May 30, 2013 at 7:19 am

• 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.

sri

May 31, 2013 at 6:43 am

3. 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.

Philip Taylor

May 31, 2013 at 1:18 pm

4. 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!

brian

October 30, 2013 at 2:18 pm

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

mauro

November 9, 2013 at 10:10 pm

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

Chris Webb

November 9, 2013 at 10:17 pm

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

Eric

December 2, 2013 at 9:16 pm

• The solve order will be dictated by the order that these calculations appear on the Calculations tab of the cube editor

Chris Webb

December 2, 2013 at 11:19 pm

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

Wayne

March 13, 2014 at 9:27 pm

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

Jesper Sommer

April 30, 2014 at 11:33 am

• 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.

Chris Webb

April 30, 2014 at 11:41 am

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

Jesper Sommer

May 1, 2014 at 9:40 am

• 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.

Chris Webb

May 1, 2014 at 9:43 am

9. 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? :-)

Jesper Sommer

May 1, 2014 at 3:19 pm

• 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.

Chris Webb

May 1, 2014 at 4:12 pm

10. 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.

Regards

Harris

Harris

May 16, 2014 at 10:51 am

• 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.

Chris Webb

May 16, 2014 at 11:05 am

• Hi Chris

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

Harris

May 16, 2014 at 1:23 pm

• 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]))
);

Chris Webb

May 16, 2014 at 1:34 pm

• 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

Harris

May 16, 2014 at 2:00 pm

• 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]))
);

Chris Webb

May 16, 2014 at 2:03 pm

• 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

Harris

May 16, 2014 at 2:31 pm

• 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].

Chris Webb

May 16, 2014 at 8:59 pm

• 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

Harris

May 17, 2014 at 10:23 am

• 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.

Chris Webb

May 18, 2014 at 4:11 pm

11. 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

Bhargav Kandala

May 19, 2014 at 5:46 pm

12. 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;

BJ

May 26, 2014 at 2:17 pm

• It should work fine with a parent-child hierarchy, you just need to scope on the key attribute of the dimension.

Chris Webb

May 26, 2014 at 9:30 pm

13. Hi Chris,
I need to add Customer dimension to have the same behaviour as item. How do I add this into the scope.
Thanks

Aleksandar

May 26, 2014 at 9:49 pm

14. 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;

Aleksandar

May 26, 2014 at 9:54 pm

• 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;

Chris Webb

May 26, 2014 at 10:19 pm

• 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.

Aleksandar

May 27, 2014 at 3:36 am

• 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.

Chris Webb

May 27, 2014 at 1:06 pm

• 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.

Aleksandar

May 27, 2014 at 5:16 pm

• I think I got you. I should have 2 calculations then. One for Date / Client scope and another for Date / Item scope right?

Aleksandar

May 27, 2014 at 5:21 pm

• Yes, exactly

Chris Webb

May 28, 2014 at 9:57 pm

15. 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

Aleksandar

May 26, 2014 at 9:58 pm

16. 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.

Abhijit

June 7, 2014 at 2:03 am

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

Chris Webb

June 7, 2014 at 10:27 pm

• 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

Abhijit

June 9, 2014 at 7:22 pm

• 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)

Chris Webb

June 9, 2014 at 9:02 pm

17. 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

Uday Kiran

June 7, 2014 at 6:39 am

• 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.

Chris Webb

June 7, 2014 at 10:30 pm

• Hi Chris,

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

Uday Kirab

June 8, 2014 at 7:54 pm

• 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.

Chris Webb

June 8, 2014 at 8:15 pm

18. 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;

Matthew

July 31, 2014 at 1:50 pm

19. 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 :)

Tabban

August 8, 2014 at 12:59 pm

• 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.

Chris Webb

August 8, 2014 at 9:07 pm

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

Tabban

August 9, 2014 at 9:00 am

• Yes, you are completely correct. Regarding performance, the calculated measure will perform better because it is doing less work.

Chris Webb

August 10, 2014 at 8:53 am

20. 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.

Wenchi Wang

January 26, 2015 at 3:45 pm

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

Chris Webb

January 26, 2015 at 9:04 pm

21. 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.

Tom Hogan

January 30, 2015 at 6:07 pm

22. I forgot to mention that B is actually the result of two other measures being divided to get a percent (B = C / D).

Thanks.

Tom Hogan

January 30, 2015 at 6:10 pm

• 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;

Chris Webb

January 31, 2015 at 3:25 pm

• 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.

Tom Hogan

March 16, 2015 at 8:38 pm

• 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 Webb

March 16, 2015 at 11:11 pm

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

Nigel Murdoch

February 19, 2015 at 12:19 am

• 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 Webb

February 21, 2015 at 4:20 pm

• 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!

Nigel Murdoch

February 22, 2015 at 5:53 pm

24. 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!

Gelder de la Ossa

February 25, 2015 at 7:53 pm

• 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!

Gelder de la Ossa

February 27, 2015 at 1:10 pm

25. 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.

Oleksandr

March 3, 2015 at 2:11 pm

• 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/

Chris Webb

March 3, 2015 at 2:17 pm

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

Oleksandr

March 3, 2015 at 2:28 pm

• 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.

Chris Webb

March 3, 2015 at 2:30 pm

26. 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

Regards,
sanjeevan

Sanjeevan

March 8, 2015 at 2:17 pm

• 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.

Chris Webb

March 8, 2015 at 5:50 pm

27. […] Aggregating the Result of an MDX Calculation Using Scoped Assignments […]

28. 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.

Tom Hogan

March 17, 2015 at 3:59 pm

29. 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

Sanjeevan

April 28, 2015 at 8:04 am

30. I tried this approach but its not working for , Can you provide me sample query

Sanjeevan

April 28, 2015 at 9:40 am

• It would be hard to provide a sample query (over what’s in the blog post) without having access to your cube.

Chris Webb

April 28, 2015 at 10:15 pm

• 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

Sanjeevan

April 29, 2015 at 6:33 am