DAX平均排名总和 [英] DAX Sum of Average to Rank

问题描述

我有一个数据透视表，可按人从powerpivot获取费率。汇率是计算所得的字段，而不是列，我需要总计来显示汇率的总和，而不是平均值。这纯粹是出于排名的目的，以显示谁拥有最高的整体比率而最低的比率。这就是我想要的字段的样子：

I have a pivot table that pulls a rate by person from powerpivot. The rate is a calculated field, not a column and I need the Grand Total to show as a SUM of the rates, not an average. This is purely for ranking purposes to show who has the highest overall rates to lowest. This is what it looks like with the field I want:

人Jan-2018 Feb-2018 Mar-2018 GrandTotal [DesiredField]
A 80％71％82％77.6％233％
B 76％[空白] 85％80.5％161％
C 82％85％79％82％246％

因此，人C在顶部，然后是A，然后是B。由于OLAP的限制，我无法创建计算字段，并且汇总值依据字段为灰色出来。如果有更好的解决方法，请告诉我。

So person C would be at the top followed by A then B. Due to OLAP limitations I can't create a calculated field and 'Summarize Values By' field is greyed out. If there is a better work around please let me know.

推荐答案

由于未提供数据模型，因此我认为您可以采取措施（计算字段），某些人和带有月-年列的日期维度。

Since there's no data model given, I assume you have a measure (calculated field), some persons and a Date dimension with a 'Month-Year' column.

您可以尝试使用SUMX函数，对给定的值表进行迭代。在这种情况下，您需要对每个月总结出计算出的[费率]。

You could try the SUMX function, it iterates over a given table of values. In this case, you need to sum up the calculated [Rate], for each 'Month-Year'.

示例：

SumRatePerMonth :=
SUMX(
     VALUES('DimDate'[Year-Month]),
     [Rate]
)

该公式会在DimDate表*的[Month-Year]列中进行迭代，计算出[每个[月-年]的费率]，并将其相加。

The formula iterates over the [Month-Year] column in the DimDate table*, calculates the [Rate] for each [Month-Year], and sums it.

注意：就像我说的，我假设您具有某种日期维度，您可以从中提取年－年列。如果我的假设是错误的，请向我们提供您的数据模型的示例或屏幕截图。

Note: Like I said, I assume you have a some sort of Date dimension, from which you pull the Month-Year column. If my assumption is wrong, please provide us with a sample or screenshot of your datamodel.

这篇关于DAX平均排名总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！