Calculate Averages with Hierarchies – Average from Leaves
By Joyce Zhou - Updated May 28, 2015
SQL View + MDX Rule
1. Average by Non-empty Leaves
We will introduce a measure 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. Create a Measure Group called Count in the model.
2. Create a Measure called Count for Count Measure Group. Set the Aggregation method as Count, as the screenshot shown below.
3. Create a Partition called Count in the model. Set the Measure Group to Count, as shown below.
4. In SQL Server Management Studio, create a view from the source partitions which include the account that you want to calculate.
5. Go back to Kepion. Change the Partition Source to the view we just created.
6. Change the Notification Table accordingly.
7. Define a MDX rule to calculate the averages.
8. Deploy the application.
2. Average by All Leaves
This calculation is very similar to the Average by Non-empty Leaves. The only difference is count all the leaf members instead of scoping it to the ones that have a value. Please compare the Count column in the picture below with the one in Average by Non-empty Leaves to understand the difference.
Follow the steps 1-3 in the Average by Non-empty Leaves section. In Step 4, create the view by join all the model hierarchies together, as shown below.
Then continue to finish step 5-8 in the Average by Non-empty Leaves section.