ssas mdx行数量除以SUM [英] ssas mdx line quantity divided by SUM

问题描述

如何在MDX中创建新列?选择的尺寸并不重要.我只想要所选数据的行数/总和.我不是mdx专家...

How do I create the new column in MDX? The dimensions selected are not important. I just want the line amount / sum for the data selected. I am not an mdx expert...

下面的当前代码:

CREATE MEMBER CURRENTCUBE.[Measures].[Weight]
 AS IIF([Measures].[Sales Line Amount AC Budget hidden] <> 0,
Divide([Measures].[Sales Line Amount AC Budget hidden]
 ,AGGREGATE(Root(),[Measures].[Sales Line Amount AC Budget hidden])),NULL), 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Sales Forecast'  ; 

他们是同一列...我只是引用一个隐藏的列，因此我可以更改其属性，而不会影响原始度量.您确定MDX会给我我想要的结果吗?可能是格式问题吗?该度量的格式为#，0.00

Hi they are the same column... I just reference a hidden column so i can change the properties on it not affecting the original measure. Are you sure the MDX will give me the result I want? Could it be a formatting issue? the format of that measure is #,0.00

推荐答案

好！ 我想我知道您想要尝试什么.使用 Root ，您可以从各个维度获得全部.

OK! I think i understand what you want try this. With Root you can get the All from all dimensions.

Divide(
  [Measures].[Sales Line Amount AC Budget hidden]
 ,AGGREGATE(
   Root()
  ,[Measures].[Sales Line Amount AC Budget hidden]
  )
)

不要以为有一个很简单的方法可以做到这一点.也许看看 Axis(1)而不是root.

Don't think there is a fairly easy way to do that. Maybe have a look at Axis(1) instead of root.

我不是只在查询中在多维数据集中尝试此操作……它确实有效.

Divide(
  [Measures].[Sales Line Amount AC Budget hidden]
 ,AGGREGATE(
   Axis(1)
  ,[Measures].[Sales Line Amount AC Budget hidden]
  )
)

因为您希望它可以与Excel一起使用.没有明确知道要权重"的尺寸的方法，没有一种明确的方法可以使它起作用. Excel可与Subcubes配合使用，这意味着Axis函数实际上返回该轴的所有成员.

Since you want this to work with Excel. There isn't a clear way to make it work without knowing exactly which dimensions you want to "Weight". Excel Works with Subcubes, that means, the Axis function actually return ALL the members from that axis.

无法准确知道MDX中实际显示了哪些成员.这应该是客户端计算.

There isn't a way to know exactly which members are being actually shown in MDX. This should be a Client side calculation.

实际上，只需选择进入OPTION的字段并转到将值显示为"，您就可以在EXCEL中非常轻松地执行此操作.有了总计百分比"，您就可以准确找到所需的百分比.

You can actually do this very easly in EXCEL simply by selection the field going to OPTION and going to "Show Values As". With "% Grand Total" will give you exactly the percentage you are looking for.

这篇关于ssas mdx行数量除以SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！