Dynamic AVG in MDX

Question

How can I calculate AVG of many members?

Let's assume that I have measure Risk. I am interested only in average risk, across many dimensions. Users:

1.Connect to cube from Excel and if they choose measure Avg Risk, they want to see average risk for selected dimensions.

2.They also want to use it in Power BI.

For that reason I look for dynamic member, that I can use in following formula:

WITH MEMBER [Measures].[Avg Risk] AS  
   Avg(  
      Dynamic member,   
      [Measures].[Risk] 
   )

to achieve this behavior. Is it possible?

Answer 1

First create two new real measures in your cube structure. Set the first measure to aggregation "Sum" of your desired column which you use for your risk measure. Set the second measure to aggregation "Count of rows" or "Count of non-empty values" of the same column.

Second create a new calculated measure with the following mdx statement:

divide([Measures].[Created SUM measure], [Measures].[Created Count measure])

This is the standard to calculate averages in ssas.