Answered by:
Wrong Totals on Calculated Fields with IIF condition

Help! I was trying to create a calculated field that would pick the HIGHER value between two different measure (fields) and rolling them up to a subcategory:
CREATE MEMBER CURRENTCUBE.[Measures].[Likely] AS
IIF([Measures].[Gross Sales]>[Measures].[Forecast],[Measures].[Gross Sales],[Measures].[Forecast]);
It worked on the leaf level, but whenever rolled up to the Product category level, then it continue to apply the same calculation (condition) rather than adding all "Likely" field (member)
Calculated member Row Labels Sales Forecast Likely Cat A Total 20 40 40 < Should be 45 Product A 5 10 10 10+10+15+10 Product B  10 10 Product C 15 10 15 Product D  10 10 Cat B Total 20 40 40 < Should be 55 Product X 10 5 10 10+35+10 Product Y  35 35 Product Z 10  10 Grand Total 40 80 80 < Should be 105 (45+60)
Question
Answers

Hi Peter
this is not a bug . Think of it a bit more. We are on Product level as currentmember so the IIF is eveluated to
Cat A Total 20 40 40 < Should be 45 Product A 5 10 10 Product B  10 10 Product C 15 10 15 Product D  10 10
but when the claculation goes on level up it does not know what was the lower level data as calcualted member are computed on the fly and the data is not stored for in cube.
So know all it knows is
Cat A Total20 40
Here IIF will generate 40 as output.
Now there are 2 ways you can handle this
1. making you calcualtion work differently at different level (You may use IIF as I said or CASE as Darren said)
2. Storing an extra column in the fact table and bring this clacualted data in the cube
You can do it by modifying the named query in the data source view to add a new column which phycially stores this lower level data in the fact table. So this Likely column will have 10,10,15,10.
When you go one level up for this measure now , you will get 45 as you want.
This means that you move your calcuation from cube to SQL query , get this to work on the granularity of your fact table , and when you aggregate any level UP it will yeild the result you are looking for because on query time it will go the granular level and sum up on this new measure.
Hope this helpsAshwani Roy
Please click the Mark as Answer button if a post solves your problem!
 Marked as answer by RaymondLeeMicrosoft employee, Moderator Thursday, July 30, 2009 6:47 AM

As Ashwani said, this is not a bug. Calculations are not totalled by design, they are always calculated in the current context.
You have 2 choices
1) You could still do this during your ETL, you would have to join your forecast and sales fact tables, but I think you could probably create the derived column in your sales table. This would slow down your process to load the data into the cube, but would give you the fastest query speed.
2) the other option would be to create a second measure in your cube, I would probably create the [Likely] measure off the Gross Sales amount in the sales fact table. Then I would create an assignment in the cube script. In contrast to calculated measures, assignments on "raw" measures will aggregate up. If you put this assigment at the very top of your calculation script (before the CALCULATE; statement) it should get aggregated up the way you desire.
eg.
Measures.[Likely] = IIF([Measures].[Gross Sales]>[Measures].[Forecast],[Measures].[Gross Sales],[Measures].[Forecast]);
CALCULATE;
... <the rest of your calculations> ...
This will easier to implement, but require a scan at the leaf level to calculate the Likely amount so it will be slower than option 1.
http://geekswithblogs.net/darrengosbell  please mark correct answers Marked as answer by RaymondLeeMicrosoft employee, Moderator Thursday, July 30, 2009 6:47 AM
All replies

If Sales and Forecast are in the same fact table I would suggest doing the IIF() logic in a CASE statement in a calculated column in your DSV or in a view in the data warehouse. It will preform better and will add up in the way you desire.
http://geekswithblogs.net/darrengosbell  please mark correct answers 
I tried to simulate this scenario using Adventure works.
First I will try to explain why is this happening. Calculated Member which you have defined is not level aware. So what happens if I look at Prod A , B,C,D it is fine.
But once you go a level up it takes all the MIN and aggregates is that is 10+10+15+10. In adventure works scenarioWITH MEMBER [Measures].[A] AS IIF([Measures].[Internet Sales Amount]>[Measures].[Reseller Sales Amount] ,[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]) select { [Measures].[Internet Sales Amount] ,[Measures].[Reseller Sales Amount] ,[Measures].[A] } on 0 ,{[Product].[Product Categories].[Category].MEMBERS} on 1 from [Adventure Works]
Internet Sales Amount Reseller Sales Amount Likely Accessories $700,759.96 $571,297.93 700759.96 Bikes $28,318,144.65 $66,302,381.56 66302381.56 Clothing $339,772.61 $1,777,840.84 1777840.839 Components (null) $11,799,076.66 11799076.66
Everything is fine here for me and the SUM of Likely is80580059.01
Now I will apply it to one level above this product level
WITH MEMBER [Measures].[Likely] AS IIF([Measures].[Internet Sales Amount]>[Measures].[Reseller Sales Amount] ,[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]) select { [Measures].[Internet Sales Amount] ,[Measures].[Reseller Sales Amount] ,[Measures].[Likely] } on 0 ,{[Product].[Product Categories].[Category].&[1].parent} on 1 from [Adventure Works]
Internet Sales Amount Reseller Sales Amount Likely All Products $29,358,677.22 $80,450,596.98 80450596.98
Same thing happened as with your query. The numbers differ by 129462.0322. Now in order to get the exact sum of what is on the Lower level , we need to make this calcualtion differnentiate level and do different stuff .
WITH MEMBER [Measures].[A] AS IIF([Measures].[Internet Sales Amount]>[Measures].[Reseller Sales Amount] ,[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]) MEMBER [Measures].[B] AS SUM({[Product].[Product Categories].[Category].MEMBERS},[Measures].[A]) MEMBER [Measures].[LEVEL] AS ([Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL ) MEMBER [Measures].[Likely] AS IIF([Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL = 1 , [Measures].[A],[Measures].[B]) select { [Measures].[Internet Sales Amount] ,[Measures].[Reseller Sales Amount] ,[Measures].[Likely] ,[Measures].[LEVEL] } on 0 ,{[Product].[Product Categories].[Category].&[1].parent} on 1 LEVEL = 1 ,{[Product].[Product Categories].[Category].MEMBERS} on 1 LEVEL = 0 from [Adventure Works]
Now for Both Levels you will get what you want .
Hope this helps you understand how will you have to modify the query to get the query in your case even though it is based on Adventure worksAshwani Roy
Please click the Mark as Answer button if a post solves your problem!

Hi Darren,
Thanks for the quick response, our data is actually from two very different system, one accounting and another from forecasting. They are only connected by customers and products (I even have to create virtual customers to make it work), and so both sales and forecast are on separate fact table with no direct relationship.
Is this a Microsoft issue or I'm just not doing something correctly. the issue is quite simple, the condition on the calculated field is also being evaluated even on the ALL level instead of just totaling it, is there a way to ask SSIS to recalculate all parent levels without specifying which dimension (slicer).
And since I want to allow user to slice and dice information in various ways, the solution below (by Mr. Roy) would cause the calculation to become too convoluted and might slow down the system.
Thanks again! 
Hi Peter
this is not a bug . Think of it a bit more. We are on Product level as currentmember so the IIF is eveluated to
Cat A Total 20 40 40 < Should be 45 Product A 5 10 10 Product B  10 10 Product C 15 10 15 Product D  10 10
but when the claculation goes on level up it does not know what was the lower level data as calcualted member are computed on the fly and the data is not stored for in cube.
So know all it knows is
Cat A Total20 40
Here IIF will generate 40 as output.
Now there are 2 ways you can handle this
1. making you calcualtion work differently at different level (You may use IIF as I said or CASE as Darren said)
2. Storing an extra column in the fact table and bring this clacualted data in the cube
You can do it by modifying the named query in the data source view to add a new column which phycially stores this lower level data in the fact table. So this Likely column will have 10,10,15,10.
When you go one level up for this measure now , you will get 45 as you want.
This means that you move your calcuation from cube to SQL query , get this to work on the granularity of your fact table , and when you aggregate any level UP it will yeild the result you are looking for because on query time it will go the granular level and sum up on this new measure.
Hope this helpsAshwani Roy
Please click the Mark as Answer button if a post solves your problem!
 Marked as answer by RaymondLeeMicrosoft employee, Moderator Thursday, July 30, 2009 6:47 AM

As Ashwani said, this is not a bug. Calculations are not totalled by design, they are always calculated in the current context.
You have 2 choices
1) You could still do this during your ETL, you would have to join your forecast and sales fact tables, but I think you could probably create the derived column in your sales table. This would slow down your process to load the data into the cube, but would give you the fastest query speed.
2) the other option would be to create a second measure in your cube, I would probably create the [Likely] measure off the Gross Sales amount in the sales fact table. Then I would create an assignment in the cube script. In contrast to calculated measures, assignments on "raw" measures will aggregate up. If you put this assigment at the very top of your calculation script (before the CALCULATE; statement) it should get aggregated up the way you desire.
eg.
Measures.[Likely] = IIF([Measures].[Gross Sales]>[Measures].[Forecast],[Measures].[Gross Sales],[Measures].[Forecast]);
CALCULATE;
... <the rest of your calculations> ...
This will easier to implement, but require a scan at the leaf level to calculate the Likely amount so it will be slower than option 1.
http://geekswithblogs.net/darrengosbell  please mark correct answers Marked as answer by RaymondLeeMicrosoft employee, Moderator Thursday, July 30, 2009 6:47 AM