none
how to add a calculated member to return a minimum value instead of the total

    Question

  • I have created a Retailer-SubRetailer hierarchy in a ssas cube similar to a Manager-Employee hierarchy relationship.  

    Every Retailer has to pay a license fee per month which is 10% of sales. 
    On certain parent Retailer only, there is a minimum license fee if the total of their subRetailers license fee does not meet the minimum requirement, like Retailer B below.

    Row Labels Sales License Fee Min License Fee Actual License Fee
    Retailer A 60,000.00 6,000.00 1000 6,000.00
      SubRetailer A1 10,000.00 1,000.00
    1,000.00
      SubRetailer A2 20,000.00 2,000.00
    2,000.00
      SubRetailer A3 30,000.00 3,000.00
    3,000.00
    Retailer B 400.00 40.00 500 500.00
      SubRetailer B1 400.00 40.00
    40.00
    Retailer C 5,000.00 500.00
    500.00
      SubRetailer C1 5,000.00 500.00
    500.00
    Grand total 65,400.00 6,040.00 1500 1,500.00


    I used the expression below to add the calculated member 'Actual License Fee'.  If the current Retailer member has an associated 'min fee' value and the total license fee is less than the 'min fee', return 'min fee'

    Case

    When IsEmpty
         ( (  [Measures].[License Fee], [Retailer].[Parent Retailer].CurrentMember ) ) 
    Then NULL
    when ( [Measures].[Min Fee], [Retailer].[Parent Retailer].CurrentMember) > 0 and
        ( [Measures].[Min Fee], [Retailer].[Parent Retailer].CurrentMember) >
        ( [Measures].[License Fee], [Retailer].[Parent Retailer].CurrentMember)

    Then ( [Measures].[Min Fee], [Retailer].[Parent Retailer].CurrentMember)
    Else ( [Measures].[License Fee], [Retailer].[Parent Retailer].CurrentMember) 
    End

    However, the Total Actual License Fee in the Grand Total Row does not add up correctly. 

    Is shows 1,500 instead of 7,000 (6000+500+500).  What have I done wrong?

    Looks like it is the sum of the 'min fee' instead of the actual License Fee.  Any suggestion?

    Wednesday, January 03, 2018 3:53 AM

Answers

  • Hi lkiris123,

    Thanks for your question.

    In this scenario, Please try to overwrite the cell for the grand total, something like below:

    SCOPE([Measures].[Actual License Fee]);
    SCOPE([Retailer].[Parent Retailer].[ALL]);
    THIS = SUM([Retailer].[Parent Retailer].[ALL].Children,
               [Measures].[Actual License Fee]);
    END SCOPE;
    END SCOPE;

    OR
    ([Measures].[Actual License Fee],[Retailer].[Parent Retailer].[ALL])
    =
     SUM([Retailer].[Parent Retailer].[ALL].Children,
               [Measures].[Actual License Fee]);


    Best Regards
    Willson Yuan
    MSDN Community Support
    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 MSDNFSF@microsoft.com

    • Marked as answer by lkiris123 Wednesday, January 03, 2018 6:26 PM
    Wednesday, January 03, 2018 4:39 AM
    Moderator

All replies

  • Hi lkiris123,

    Thanks for your question.

    In this scenario, Please try to overwrite the cell for the grand total, something like below:

    SCOPE([Measures].[Actual License Fee]);
    SCOPE([Retailer].[Parent Retailer].[ALL]);
    THIS = SUM([Retailer].[Parent Retailer].[ALL].Children,
               [Measures].[Actual License Fee]);
    END SCOPE;
    END SCOPE;

    OR
    ([Measures].[Actual License Fee],[Retailer].[Parent Retailer].[ALL])
    =
     SUM([Retailer].[Parent Retailer].[ALL].Children,
               [Measures].[Actual License Fee]);


    Best Regards
    Willson Yuan
    MSDN Community Support
    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 MSDNFSF@microsoft.com

    • Marked as answer by lkiris123 Wednesday, January 03, 2018 6:26 PM
    Wednesday, January 03, 2018 4:39 AM
    Moderator
  • Hi Willson,

    The [Actual License Fee] was added as a calculated member at the cube level.  Where do I put in the code to modify the Grand total? 

    Should I add another Grand total calculated measure at the cube level?  I tried but it showed #VALUE! and it has to be added as a separate column.

    Sorry I am new to SSAS. 

    Wednesday, January 03, 2018 4:32 PM
  • I found out that from the Calculations tab, I can switch to script view and add the code at the end of the script.

    Thank you.  It works.

    Wednesday, January 03, 2018 6:26 PM
  • Further to my question above, I found another problem once I added another level of hierarchy

    Row Labels Sales License Fee Min License Fee Actual License Fee
    Retailer Group 1 60,000.00 6,000.00 1000 6,000.00
      Retailer A 60,000.00 6,000.00 1000 6,000.00
        SubRetailer A1 10,000.00 1,000.00
    1,000.00
        SubRetailer A2 20,000.00 2,000.00
    2,000.00
        SubRetailer A3 30,000.00 3,000.00
    3,000.00
    Retailer Group 2 5,400.00 140.00 500 140.00
      Retailer B 400.00 40.00 500 500.00
        SubRetailer B1 400.00 40.00
    40.00
      Retailer C 1,000.00 100.00
    100.00
        SubRetailer C1 1,000.00 100.00
    100.00
    Grand total 65,400.00 6,040.00 1500 6,140.00

    The subtotal of 'Retailer Group 2' does not reflect the change of the license fee in Retailer B.  It should be 600 (Retailer B 500 + Retailer C 100) instead of 140.  How do I modify the value for each parent group? 


    Wednesday, January 03, 2018 11:44 PM
  • Hi lkiris123,

    Thanks for your response.

    I thought measure [Min License Fee] will only apply at Parent Retailer level. If [Min License Fee] also apply at Retailer Group level, then you should apply this similar logic at Parent Retailer level for Retailer Group level.


    Best Regards
    Willson Yuan
    MSDN Community Support
    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 MSDNFSF@microsoft.com

    Friday, January 05, 2018 8:46 AM
    Moderator