none
Aggregating the calculated measure RRS feed

  • Question

  • Hello Team,
    I need an urgent solution for the below problem. Your immediate help is hoghly appreciated.
    I would explain in steps for clear:
    1. I have a Measure, [Measure] which is  calculated from three different measures as [Qty1]+[Qty2]-[Qty3]
    2. [Measure4] is a running sum calculated from the function PeriodsTodate() of [Measure]( [Qty1]+[Qty2]-[Qty3])
    3. [Measure5]  is positive values of [Measure4] which users wants to see as final results.This is defined as  (IF[Measure4] > 0 then [Measure4] ELSE NULL)
    Now My problem is here with aggregation for the measure [Measure5] which evaluates on excel pivot level. For Time dimension this is OK. But for Product Dimension, they want to see sum of its children than pivot level evaluation. Since calcuted measure do not aggragate up by default, I have applied the below logic to solve this problem.
    I have created a dummy real measure, [Measure6], and assign the values of [Measure5] to the leaf level of Production dimension like below:
    SCOPE (Leaves(Product), [Measure6]))
    THIS = [Measure5]
    END SCOPE:

    This solution works fine. But using Leaves() function is creating a huge performance issues in user reports.
    So we decided to move the changes to the Database level:
    In database we created the measure [Measure6], completing all the steps above and values looks fine. But bringing database values [Measure6] to cube, again  we will have aggregation problem.  Because we need to  define the aggregation function in the cube strcture for measures added from the datasource. Product dimension is SUM() at the higher level, which is OK. But for Time dimension this should not aggregate up, as this is running sum caculated now in the database(which is results of PeriodsTodate()), and should display as is received from the data source.
    Now I am running out of solutions. Can you please help me to implement this in alternate way?

    Thanks and Regards,
    Prema
    Thursday, November 22, 2018 3:54 PM

All replies

  • Hi PXPRXR,

    -->>But for Time dimension this should not aggregate up, as this is running sum caculated now in the database(which is results of PeriodsTodate()), and should display as is received from the data source.

    Did you try to add Scope for Time Dimension to the Calculated Measure? Would you please share sample data with us if possible?

    Regards,

    Pirlo Zhang 


    MSDN Community Support<br/> Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Tuesday, November 27, 2018 1:32 AM