# 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 3, 2018 3:53 AM

• 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,
END SCOPE;
END SCOPE;```

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

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 Wednesday, January 3, 2018 6:26 PM
Wednesday, January 3, 2018 4:39 AM

### 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,
END SCOPE;
END SCOPE;```

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

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 Wednesday, January 3, 2018 6:26 PM
Wednesday, January 3, 2018 4:39 AM
• 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 3, 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 3, 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 3, 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 5, 2018 8:46 AM