英文:
AverageX of SubCategory in PowerBI DAX
问题
我有一个矩阵可视化，其中我在行级别显示类别和子类别。
我试图在总计行和行总计上显示AverageX。
我能够在总计级别显示正确的值，但在行总计级别显示AverageX的正确值时遇到问题。
考虑下面的截图作为示例：
在上面的示例中，突出显示的值是不正确的。
在ABC类别的总计字段中，我希望通过执行(4 + 2.84)/2 = 3.42来显示3.42作为结果，而不是2.97。
我尝试了很多方法来在DAX中实现这一点。
IF(ISFILTERED(Table),AVERAGEX(VALUES(Table[Category]),[Result]),[Result])
我还尝试使用SUMMARIZE，但在行级别也不起作用：
VAR SummaryTable =
SUMMARIZE(
'Table',
'Table'[Category],
'Table'[SUBCategory]
)
VAR subCateogryTotal = AVERAGEX(
SummaryTable,
[Result]
)
注意：上述DAX代码是用于数据分析表达式（DAX）的，用于在Power BI等工具中进行数据分析和可视化。
英文:
I have Matrix visual in which I am showing Category and Sub-Category on row level.
I am trying to show AverageX at grand total row and at row total.
I am able to show correct value at grand total level but I am facing issue showing correct value of AverageX at row total level.
Consider below screenshot for example :
In the above example highlighted values are not correct.
In the Total field of ABC category, I would like to show 3.42 as a result by doing (4 + 2.84)/2 = 3.42 instead of 2.97.
I have tried quite a few ways to achieve this in DAX.
IF(ISFILTERED(Table),AVERAGEX(VALUES(Table[Category]),[Result]),[Result])
I also tried by using SUMMARIZE but it is not wokring either at row level :
VAR SummaryTable =
SUMMARIZE(
'Table',
'Table'[Category],
'Table'[SUBCategory]
)
var subCateogryTotal = AVERAGEX(
SummaryTable,
[Result]
)
答案1
得分: 1
你能尝试这种方式：
Measure =
AVERAGEX(
VALUES('Table'[SubCategory]),
[Result]
)
英文:
Could you try this way:
Measure =
AVERAGEX(
VALUES('Table'[SubCategory]),
[Result]
)
答案2
得分: 0
如果有人需要的话，这里是解决方案。我已经添加了内联注释来解释代码。结果是通过计算ColumnA/ColumnB得出的。
var totCategory = DISTINCTCOUNT(Table[Category])//获取类别的总数
RETURN if(totCategory = 1,
AVERAGEX(VALUES(Table[SubCategory]),[Result]), //在子类别级别显示结果
AVERAGEX(VALUES(Table[Category]),AVERAGEX(VALUES(Table[SubCategory]),[Result]))) //计算总行的平均平均值
英文:
In case any one needed, here is the solution. I have added inline comment to explain the code. Result is measure which derives by doing ColumnA/ColumnB.
var totCategory = DISTINCTCOUNT(Table[Category])//get total count of category
RETURN if(totCategory = 1,
AVERAGEX(VALUES(Table[SubCategory]),[Result]), //to show result on sub-category level
AVERAGEX(VALUES(Table[Category]),AVERAGEX(VALUES(Table[SubCategory]),[Result]))) // calculate average of average for grand total row
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论