There are more ways to aggregate values along a hierarchy than you might expect! In this article, we'll use price as an example to explore how it can be aggregated along the Product Hierarchy in four different ways.
In the sample Form below, users enter sale prices at the SKU level. You can see we have different ways to calculate the averages at the Brand and Category levels.
The following picture shows the formula for each calculation along the hierarchy.
In order to implement an average calculation, one must first determine the correct behavior. Once the right behavior is identified, you will then need to leverage a combination of both MDX and SQL to implement the solution.
Average Non-Empty Leaves
First, we'll create a new Measure Group called Count. When there is a record in the Writeback table, we will count that as 1. The average would then be the Value Measure divided by the Count Measure, at any level of the hierarchy.
1. Go to the Measure Groups node in your target Model, and select Add.
2. Enter Count for the name, and click OK.
3. Go to Measure and select Add.
4. Configure the Measure as follows:
- Name as Count
- Measure Group as Count
- Aggregation as Count
- Type as Float
5. Click OK.
6. Go to the Data node of your target Model and click Add.
7. Configure the partition as follows:
- Partition Name as Count
- Measure Group as Count
- Storage Mode as ROLAP
8. Click Save.
9. Open SQL Server Management Studio and connect to your SQL Server instance.
10. Select New Query to create a View from the source partitions. It must include the target account you're using for the calculation.
CREATE VIEW [USR-RevenueAssumption_COUNT]
AS
SELECT DISTINCT
[AccountID],
[TimeID],
[EntityID],
[ProductID],
[ScenarioID],
1 [Count],
1 [Status]
FROM [dbo].[F_Revenue-Assumption_CoreMG_Writeback] F
WHERE F.AccountID = (SELECT MemberId FROM D_Account DA WHERE DA.MemberLabel = N'Price')
11. Return to the Count data partition and go to Settings.
12. Select 
in the Table or View field.
13. Select Use custom data source from the Data Source drop-down, enter USR-RevenueAssumption_Count for the Table or View field, and click OK.
14. Repeat steps 12 and 13 for the Notification Table field. However, it should reference F_Revenue-Assumption_CoreMG_Writeback.
This is the final result:
15. Go to the Rules node of the target Model and click Add.
16. Configure Rule as follows:
- Name as Aggregation
- Copy From as Default
- Rule Type as MDX
17. Click Save.
18. Define an MDX Rule to calculate the averages.
SCOPE
(
{[Account].[Account].[Price]}
,[Measures].[Value]
);
THIS = [Measures].[Value] / [Measures].[Count];
END SCOPE;
19. Click Save and deploy the Application.
Average All Leaves
This calculation is very similar to the one used in the Average Non-Empty Leaves section. The only difference is that we'll count all the leaf members instead of scoping it to just the ones with values. Please compare the Count column below with the one in Average Non-Empty Leaves to understand the difference.
Follow steps 1-10 in the previous section; but when creating the View in SSMS, join all the model hierarchies together, as shown below.
CREATE VIEW [USR-RevenueAssumption_COUNT]
AS
SELECT DISTINCT
HA.MemberID [AccountID],
HT.MemberID [TimeID],
HE.MemberID [EntityID],
HP.MemberID [ProductID],
HS.MemberID [ScenarioID],
1 [Count],
1 [Status]
FROM H_Account_Account HA
INNER JOIN H_Time_Months HT
ON 1 = 1
INNER JOIN H_Entity_Entity HE
ON 1 = 1
INNER JOIN H_Product_Product HP
ON 1 = 1
INNER JOIN H_Scenario_Scenario HS
ON 1 = 1
WHERE HA.MemberId = (SELECT MemberId FROM D_Account DA WHERE DA.MemberLabel = N'Price')
Then, follow the rest of the steps in the Average by Non-Empty Leaves section.
Average Non-Empty Children
For this calculation, we will define the aggregation at the non-leaf level in the hierarchy in the MDX Rule, dividing the aggregation value by the count number of non-empty children.
Create the MDX Rule as follows:
SCOPE
(
{[Account].[Account].[Cost]},
--SCOPE ALL THE NON-LEAF MEMBERS IN PRODUCT HIERARCHY
{DESCENDANTS([Product].[Product Hierarchy].[All], 3, BEFORE)},
[Measures].[Value]
);
--CALCULATION
THIS = Aggregate([Product].[Product Hierarchy].CurrentMember.Children)
/
COUNT(NONEMPTY([Product].[Product Hierarchy].CurrentMember.Children));
END SCOPE;
Average All Children
This calculation is very similar to the one used in Average by Non-Empty Children. The only difference is the divisor value should be the count of all children of the given member.
Define the MDX rule as follows. Notice the difference in the highlighted area.
SCOPE
(
{[Account].[Account].[Cost]},
--SCOPE ALL THE NON-LEAF MEMBERS IN PRODUCT HIERARCHY
{DESCENDANTS([Product].[Product Hierarchy].[All], 3, BEFORE)},
[Measures].[Value]
);
--CALCULATION
THIS = Aggregate([Product].[Product Hierarchy].CurrentMember.Children)
/
COUNT([Product].[Product Hierarchy].CurrentMember.Children);
END SCOPE;