none
Wrong Totals on Calculated Fields with IIF condition

    Question

  • 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 sub-category: 

    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)

    Monday, July 20, 2009 11:38 PM

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 Total
    20 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 helps


    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, July 21, 2009 3:41 PM
    Answerer
  • 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
    Tuesday, July 21, 2009 10:04 PM
    Moderator

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
    Tuesday, July 21, 2009 12:51 AM
    Moderator
  • 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 scenario

    WITH			
    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 is
    80580059.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 works


    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, July 21, 2009 12:55 AM
    Answerer
  • 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!

    Tuesday, July 21, 2009 3:26 PM
  • 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 Total
    20 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 helps


    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, July 21, 2009 3:41 PM
    Answerer
  • 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
    Tuesday, July 21, 2009 10:04 PM
    Moderator