none
ABS(CalcMeasure) Sum is ignored in Excel

    Question

  • Hello Forum!

    I have recently come across a strange behavior in Excel when a sum of absolute values (formulated in a Calculated Measure) is treated like real numbers.

    For example, given a calculated measure that is something like:

    ABS([Measures.CasesOrdered]-[Measures].[CasesSold])

    where there could be values in that formula like:

    ABS(30-25)  would equal 5

    ABS(3-25)  would equal 22

    ABS(49-64)  would equal 15

    I am noting in Excel that the Grand Totals are being treated as real numbers so instead of the expected sum of 42, Excel appears to be ignoring the Absolute values and so I am getting a sum of 32.

    Curiously enough, if I remove the ABS function from the CM the results are as expected. Has anyone here seen or experienced this behavior before? If so, were you able to force the correct results using some alternative methods?

    I read a similar post but the desired results were just the opposite of what I have to acheive. The suggestion was to create calculated fields within the fact table in the DSV, which would probably be worth a try if both of the measures existed in the same fact table but that isn't the case here.

    Thanks!

    Wednesday, February 09, 2011 12:27 AM

All replies

  • Okay!

    So after some research and trial and error I have finally managed to manually manipulate the behavior of the Grand Total value in my code and force it to SUM the Absolute Values. My solution was to hide the Measure that initially calculated the ABSOLUTE value, create another measure to get the Absolute Value without using the ABS function at the the Grand Total (ALL) level and add a scope:

    CREATE MEMBER CURRENTCUBE.[Measures].[Forecasted Sales (Absolute)]
    AS
    IIF([Products].[Product Hierarchy].CurrentMember.Level.Ordinal = 0,
    IIF(([Measures].[Regular Cases]-[Forecasted Sales])<0,
    ([Forecasted Sales]-[Measures].[Regular Cases])
    ,([Measures].[Regular Cases]-[Forecasted Sales]))
    ,[Measures].[Forecasted Sales ABS])

    SCOPE ([ForecastingMonth].[Forecast Month].[All]
         ,{[Measures].[Forecasted Sales (Absolute)]}); 
    THIS = Sum([ForecastMonth].[Forecast Month].[All].children); 
    END SCOPE; 

    Crude but effective.

    BUT! I still have 1 small issue and that is that the Cube seems like it is dropping the 3 decimal point precision of my values. I have tried everything including CASTING the values to NUMERIC at the datasource, changing the datatype to CURRENCY in the Cube, clearing the cube cache, changing the FORMAT STRING and nothing seems to fix the problem.

    I have read white papers regarding the sometime inaccurate results that SSAS can sometimes show because of caching and how precision of numerical values can be a result but nothing I've tried so far is resolving the problem.

    Has not anyone ever dealt with Absolute values in SSAS? I can't believe I am the only one to see this problem so I'd really appreciate any input.

    Thanks!

    Friday, February 11, 2011 4:26 AM
  • Hi,

    this may be very trivial but could you solve your problem by creating a new column in your DSV and calculate the Abs-Value there
    then create a physical measure on top of that new column and let the engine aggregate it?

    this would solve all your issues, would perform better and would work in all conditions (subselects, etc.)

    hth,
    gerhard


    - www.pmOne.com -
    Friday, February 11, 2011 7:58 AM
  • Hi Gerhard,

    I really appreciate your response to my problem. Usually when I don't receive any reply I have later found that the issue is specific to my environment and so no one else has comments which is totally understandable.

    With respect to your suggestion for creating a new column in the DSV, that is a very legitimate thought and one that I have entertained. The only problem is that the values that I require an absolute value for are derived by the subtraction of values between 2 fact tables. So for it to effectively work, I would have to another table or view that would combine 2 fact tables already existing in the DSV with possibly a 3rd table included in the join to get the calculated column. As opposed to ideally having a calculated field within one fact table

    Then subsequently create the physical measure as you suggested on top of the fact column.

    Its a great idea that I did consider but it seemed to me to be a process that would create a redundancy of data that already exists in the Cube as well as likely having potential for degrading the performance during the processing. Maybe not by much but it seemed a long way around to a otherwise seemingly simple solution. (if there is one) ;-)

    Does my logic make any sense to you? Or am I possibly over-thinking the suggestion? If I can't get any other suggestions for the problem, I may just have to go with it.

    Let me know.

    Friday, February 11, 2011 2:43 PM
  • ok, let me consolidate the information i have so far:
    you got 2 measures from different MGs: [Forecasted Sales] and [Measures].[Regular Cases]
    those 2 MGs share some dimensions but with different granularities
    you need to calculate ABS([Regular Sales] - [Forecasted Sales]) on the lowest common granularity and sum up the result to all higher levels

    please correct me if i misunterstood something

    i would do the following:
    add a new empty MG to your cube and link it to the lowest granularity the other 2 MGs share
    create a new SUM-measure in your MG and call it [Abs-Value] or whatever you want
    (note that you do not need any fact-rows in this MG, we just use it overwrite the physical measure later in the script)

    in your MDX-Script do the following:
    SCOPE([Measures].[Abs-Value]);
        SCOPE(LEAVES());
            this = ABS([Regular Sales] - [Forecasted Sales]);
        END SCOPE;
    END SCOPE;

    well, basically thats it!
    as we overwrite a phyiscal measure, the aggregation is done by the engine and will work for all higher levels
    you may hide that member and create a calculated member that links to it in an other MG

    hth,
    gerhard


    - www.pmOne.com -
    • Marked as answer by mindscape777 Saturday, February 12, 2011 1:59 AM
    • Unmarked as answer by mindscape777 Saturday, February 12, 2011 7:38 PM
    Friday, February 11, 2011 5:00 PM
  • Gerhard,

    You are dead on for the requirements and although I kinda get the concept of your instructions, a few things you mentioned had me a bit baffled. For instance, you said to add a new empty measure group to my cube and link it to the lowest granularity that the other 2 MG's shared.

    The only way I can link a MG to 2 other MG's is through a dimension but if the MG is empty I can't link it.  And I was further confused when you then said to create another new SUM-measure in the first MG and name it because the 1st instruction wasn't really clear to me.

    I am going to mark your response as answered because you have been very helpful and from your last response, I see that you do have a clear understanding of the problem; and it has given me another way of approaching the issue. I just don't clearly understand your instructions.

    But I'm going to see if I can't figure out what your suggesting as a solution.

    Thanks!
    Saturday, February 12, 2011 1:59 AM
  • I did a little footwork regarding the suggestion and think I've grasped the concept of the empty measure group now.

    Here were my steps:

    1. Created a named query in my DSV that contained the column I required with a default DateKey Column having a value of '1900-01-01 00:00:00.000'. The other column returned nothing (NULL)

    2. I linked both of the fact tables in the DSV using the DateKey

    3. I created a new MG using the named query and created a SUM measure using the column that had no value.

    4. I established the Dimension Usage of the new Measure using an existing dimension that both of the Real Measure Groups shared at the lowest granularity level.

    I then added the scope as suggested and my cube keeps failing during processing with the consistent error:

    Leaves function for attributes with mixed granularity in different measure groups is not supported.

    I researched the error and found the explanation that the error is related to the LEAVES function when its used on a dimension that is related to measure groups at different granularities.

    So now I'm stumped because I thought that this was what its supposed to do. "the aggregation is done by the engine and will work for all higher levels"

    So what am I missing? Is my setup wrong or should I be using something other than the LEAVES function???

    Saturday, February 12, 2011 11:48 AM
  • I figured out what my problem was and set this up again.

    I created an empty view in my Datasource and changed the NQ in my DSV to select from the view with a condition that returned "No Rows" but had the names of the key fields the other 2 MG's shared. I also removed all relational joins to this NQ that I'd setup previously.

    I then created a new SUM-measure in my MG, called it [Abs-Value] and created a SCOPE for the new measure:

    SCOPE([Measures].[Abs-Value]);
        SCOPE(LEAVES());
            this = ABS([Regular Sales] - [Forecasted Sales]);
        END SCOPE;
    END SCOPE;

    This time No Errors! Hurray!

    But no values for the new Measure either. :-( If I remove the inner SCOPE I get the values but the initial problem is back as well. So here is what I'm seeing in Excel using a pivot table against my SSAS Database, maybe someone can offer some reasonable solution or confirm is this is simply BY-DESIGN:

    Regular Cases Forecast Cases Abs-Value Difference AbsoluteDiff
    73.667 50.000 23.667 23.667 23.667
    5.082 83.333 78.251 -78.251 78.251
    16.250 .000 16.250 16.250 16.250
    7.250 .166 7.084 7.084 7.084
    -1.000 25.000 26.000 -26.000 26.000
    .000 1.000 1.000 -1.000 1.000
    .000 1.000 1.000 -1.000 1.000
    8.500 2.666 5.834 5.834 5.834
    10.333 5.000 5.333 5.333 5.333
    .000 1.000 1.000 -1.000 1.000
    .000 50.000 50.000 -50.000 50.000
    120.082 219.165 99.083 -99.083 215.419
     

    The last 2 columns are calculations I created in Excel.
    The Difference column = [Regular Cases] - [Forecast Cases] at each row
    The Absolute Difference column = ABS([Regular Cases] - [Forecast Cases]) at each row

    What I believe SSAS is doing is performing a SUM against the totals. ABS(120.082-219.165) which gives an absolute difference.

    What I need is for it to perform a SUM against the actual ABSOLUTE values for each record, not the Totals. Which is what the Absolute Difference column is doing.

    Is this possible in SSAS??? I have a requirement that mandates this in the Cube and so far I have nothing that works despite many hours and suggestions.

    PLZ HELP!

    Saturday, February 12, 2011 7:18 PM
  • do you 2 MGs really only share the Time-Dimension?
    you need to use all shared dimensions for your new MG

    in your example-query above, what did you put "ON ROWS"? was it the time-dimension (meaning the linked dimension) or a different one?

    greets,
    gerhard


    - www.pmOne.com -
    Monday, February 14, 2011 8:05 AM