我们使用Azure Analysis Services来托管我们的多维数据集。我们在通过 EXCEL 读取数据时遇到了问题。
问题是总计(和小计)计算不正确。最初,我们在 EXCEL 中发现了这个问题。数据透视表总计行显示的金额不正确,应该是所有行的总和。后来发现我们也可以在 MDX 查询中重现 de 问题。
列值由计算度量给出,该度量值计算一年前产品的已售商品(数量)。
例如,对于产品A,我们回顾一年前,并对所有售出的物品进行总和。如果我们有一个特定年份的过滤器,这很好用。失败是当我们需要一个小计或一个没有应用任何年份过滤器的总计时,或者当我们在过滤器中选择多个年份时。
以下是用于计算交易量的 DAX 公式:
IF (
HASONEVALUE( 'Calendar'[Year] ),
CALCULATE(
[volume],
FILTER(
ALL( Calendar ),
'Calendar'[Year] = VALUES( 'Calendar'[Year] )-1
&& 'Calendar'[ISO_DAY] <= MAX( 'Calendar'[ISO_DAY] )
)
),
IF(ISBLANK([Turnover]),BLANK(),CALCULATE([Volume],SAMEPERIODLASTYEAR(DATESBETWEEN('Calendar'[PK_Date],[MinSalesDateThisYear],[MaxSalesDate]))))
)
- 营业额 - 总和并按商店过滤
- 体积 - 总和并按商店筛选
- 最小销售日期今年 - 当年的第一天
- MaxSalesDate - 我们销售产品的最后一天
声明 SSMS 分析器并捕获了 EXCEL 生成的内容。
没有年份过滤器(总计不正确):
SELECT
{
[Measures].[Volume LYTD]
} DIMENSION PROPERTIES parent_unique_name, hierarchy_unique_name ON COLUMNS,
NON EMPTY HIERARCHIZE(
ADDCALCULATEDMEMBERS(
{
DRILLDOWNLEVEL({
[Products].[Product].[All]
})
}
)
) DIMENSION PROPERTIES parent_unique_name, hierarchy_unique_name ON ROWS
FROM (SELECT
(
{
[Products].[Product].&[1],
[Products].[Product].&[2],
[Products].[Product].&[3],
[Products].[Product].&[4],
[Products].[Product].&[5],
...
}
) ON COLUMNS
FROM [MyCube])
WHERE (
[Calendar].[YW].[All]
) CELL PROPERTIES value, format_string, language, back_color, fore_color,
font_flags
----- Volume LYTD
All 126715
Product1 26337
Product2 14525
Product3 9602
Product4 42492
Product5 28494
Product6 5154
使用年份筛选器(正确的总计):
SELECT
NON EMPTY HIERARCHIZE(
ADDCALCULATEDMEMBERS(
{
DRILLDOWNLEVEL({
[Products].[Product].[All]
})
}
)
) DIMENSION PROPERTIES parent_unique_name, hierarchy_unique_name ON COLUMNS
FROM (SELECT
(
{
[Products].[Product].&[1],
[Products].[Product].&[2],
[Products].[Product].&[3],
[Products].[Product].&[4],
[Products].[Product].&[5],
...
}
) ON COLUMNS
FROM [MyCube])
WHERE (
[Calendar].[YW].[Year].&[2018],
[Measures].[Volume LYTD]
) CELL PROPERTIES value, format_string, language, back_color, fore_color,
font_flags
All roduct1 Product2 Product3 Product4 Product5 Product6 Product7 Product8 Product9 Product10
150060 3352 11545 24037 4 11848 4 4 55088 35049 9129
我似乎无法理解这里发生了什么。我还发现一篇文章 MS 指出,由于他执行的操作顺序,EXCEL 计算总计不正确,但由于我可以在 MDX 查询中重现这一点,我怀疑是 DAX 函数或多维数据集的东西。
有人对我下一步能做什么有任何想法吗?
另外,我知道我可能没有提供一些信息,所以请问。我很乐意提供它们。
注意:我知道有一个帖子有类似的问题,但我认为这个问题有点不同,而且我不想劫持那个帖子。
尝试按年份计算,然后汇总:
SUMX(
VALUES( 'Calendar'[Year] ),
CALCULATE(
[volume],
FILTER(
ALL( Calendar ),
'Calendar'[Year] = VALUES( 'Calendar'[Year] )-1
&& 'Calendar'[ISO_DAY] <= MAX( 'Calendar'[ISO_DAY] )
)
)
)