html-table - 在 MDX 中查找与目标的销售比率

Question

我有一个 MDX 查询产生这个值

+------------+---------+--------------+--------+-- --------------+------+
| | 销售累计 | 目标销售比率 |
+ +---------+--------------+--------+------------- ---+-------
| | A镇 | B镇| C镇 | A镇 | B镇| C镇 |
+------------+---------+--------------+--------+-- --------------+------+
| 第一季 | 10 | 20 | 30 | ? | ? | ? |
| 第 2 季 | 30 | 40 | 60 | ? | ? | ? |
+------------+---------+--------------+--------+-- --------------+------+

我真的不知道如何在 MDX 中计算以获得目标的销售比率，计算比率只需将销售累积（度量）与每个城镇目标相除

我的地理维度是这样的，

+----------+----+
| Dim_Geography |
+---------------+
| 省份 |  
| 区 |
| 城镇 |
| 目标 |
+---------------+

目前这是我的销售累积 mdx 脚本..

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

标签： html-tablessasmdxmultidimensional-cube

Answer 1

如果目标是地理维度的属性，则可以将其提取到度量中，然后将两者分开：

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