Find Sales Ratio To Target in MDX

user_3074615 0
Viewed 91

I have an MDX query resulting this value

+-----------+----------+--------------+--------+----------------+------+
|           |     Sales Cumulative        |      Sales Ratio To Target |
+           +----------+--------------+--------+----------------+-------
|           |  Town A  | Town B  | Town C |  Town A | Town B  | Town C |
+-----------+----------+--------------+--------+----------------+------+
| Quarter 1 |   10     |   20    |   30   |    ?    |    ?    |    ?   |
| Quarter 2 |   30     |   40    |   60   |    ?    |    ?    |    ?   |
+-----------+----------+--------------+--------+----------------+------+

I dont really know how to calculate in MDX to get the sales ratio to target, To calculate the ratio is simply divided the sales cumulative (measure) with the each town target

My geography dimension look like this,

+----------+----+
| Dim_Geography |
+---------------+
| Province      |  
| District      |
| Town          |
| Target        |
+---------------+

Currently this is my mdx script for sales cumulative..

WITH 
  MEMBER [Measures].[SalesCumulative] AS 
    Sum
    (
      NULL : [Date].[Quarter].CurrentMember
     ,[Measures].[Sales]
    ) 
SELECT 
    {[Measures].[SalesCumulative]}
  * 
    Descendants
    (
      [Geografi].[District].[All],
     ,AFTER
    ) ON 0
 ,{Descendants([Date].[Quarter].[All],,AFTER)} ON 1
FROM [Cube];
multidimensional-cube mdx ssas html-table
1 Answers

If the target is a property of the geo dimension then maybe extract that into a measure and then divide the two:

WITH 
  MEMBER [Measures].[SalesCumulative] AS 
    Sum
    (
      NULL : [Date].[Quarter].CurrentMember
     ,[Measures].[Sales]
    ) 
  MEMBER [Measures].[Target] AS 
    [Geografi].[District].[District].CurrentMember.Properties("Target")  
  MEMBER [Measures].[Ratio] AS 
     [Measures].[SalesCumulative] / [Measures].[Target]
SELECT 
    {[Measures].[SalesCumulative],
     [Measures].[Ratio] }
  * 
    Descendants
    (
      [Geografi].[District].[All],
     ,AFTER
    ) ON 0
 ,{Descendants([Date].[Quarter].[All],,AFTER)} ON 1
FROM [Cube];
user_11798800
Related