Find Sales Ratio To Target in MDX

Question

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];

hello gill23 - do you have any `mdx` scripts that you have attempted to solve this? — whytheq, Aug 11 '20 at 11:40
@whytheq, I edited my question above, currently my script only showing the sales cumulative, I thought for sales ratio I need to calculated another measure, but dunno how — gill23, Aug 12 '20 at 06:49

score 1 · Answer 1 · answered Aug 29 '20 at 15:41

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];

Find Sales Ratio To Target in MDX

1 Answers1