Search code examples
ssasmdxmdxstudiomdx-query

MDX Calculated Measure

Below is my requirement in the picture

Sample Data

The 1st table is at least granularity.The avg is calculated by Date + Place.So the avg is 565 = (2865/5).

Coming to the second table.The avg for place 702 is 114 which is right and for 704 it is 866 which is also right.But the final ans is the same as the avg for all 5 records.

But my output should be like avg at Date + Place level for single Date + Place but when two places are selected it should avg at place level but sum at the total output..the final value should be 980 (sum(114+866))

Can someone have a solution for this?

Solution

  • As with any average in SSAS Multidimensional you will need to create two physical measures. The first is a Sum of A which is AggregateFunction=Sum. The second is a Count of Rows which is AggregateFunction=Count.

    The quicker way to implement your calculation is with this calculated measure:

    SUM(
 EXISTING [Place].[Place].[Place].Members,
 DIVIDE( [Measures].[Sum of A], [Measures].[Count of Rows] )
)

    However if you want to get better performance and properly handle multi-select filters then create a physical measure called Avg A which is AggregateFunction=Sum off a new column in your SQL table which is always 0. Then overwrite the values in that measure with the following scope statement:

    SCOPE([Place].[Place].[Place].Members);
     [Measures].[Avg A] = DIVIDE( [Measures].[Sum of A], [Measures].[Count of Rows] );
END SCOPE;