英文:
Power BI Dax Adding a Variance Column in a matrix
问题
我在Power BI仪表板中有一个矩阵可视化，显示平均学生分数，如下所示。
我想添加一个显示方差的列，如下所示。
我知道如何使用DAX计算方差，但我无法将其添加为一个列。我只能将方差添加为两列，分别位于两个学年旁边（但我只需要一个方差列！），就像我的第二个表格一样。
原始数据如下。
有任何想法吗？
谢谢。
英文:
I have matrix visual in a power bi dashboard that shows the average students' scores which looks like below.
I would like to add a column that shows variance which looks like below.
I know how to calculate the variance with DAX but I was not able to add it as one column. I could only add variances as 2 columns next to each of the 2 school years (where I only need ONE variance column!) like my second table.
This is how the raw data looks like.
Any idea is appreciated.
Thanks.
答案1
得分: 1
一种方法是向您的模型添加一个特殊度量，如果选择了单个学校和年份，则返回分数，否则返回方差。然后，分数可以是矩阵中的列小计。例如：
MatrixMeasure = IF(HASONEVALUE(SchoolScore[School]) && HASONEVALUE(SchoolScore[Year]),
SUM(SchoolScore[Score]),
CALCULATE(SUM(SchoolScore[Score]), LASTNONBLANK(SchoolScore[Year], SUM(SchoolScore[Score]))) - CALCULATE(SUM(SchoolScore[Score]), FIRSTNONBLANK(SchoolScore[Year], SUM(SchoolScore[Score])))
)
英文:
One way is to add a special measure to your model that returns the score if a single School and year is selected, and the variance otherwise. Then the Score can be a column subtotal in the matrix. EG
MatrixMeasure = if (HASONEVALUE(SchoolScore[School]) && HASONEVALUE(SchoolScore[Year]),
sum(SchoolScore[Score]),
CALCULATE(sum(SchoolScore[Score]),LASTNONBLANK(SchoolScore[Year],sum(SchoolScore[Score])))-CALCULATE(sum(SchoolScore[Score]),FIRSTNONBLANK(SchoolScore[Year],sum(SchoolScore[Score]))))
答案2
得分: 1
不要使用矩阵，而是使用一个表格，自行计算列：
CALCULATE(
AVERAGE('Table'[Score]),
'Table'[Year] = "Year 2020/2021"
)
和
CALCULATE(
AVERAGE('Table'[Score]),
'Table'[Year] = "Year 2021/2022"
)
以及
DIVIDE(
STDEV.P('Table'[Score]),
AVERAGE('Table'[Score])
)
组合在一个表格可视化中。
英文:
Don't use a matrix, use a table table and calculate the columns yourself:
Year 2020/2021 =
CALCULATE(
AVERAGE('Table'[Score]),
'Table'[Year] = "Year 2020/2021"
)
and
Year 2021/2022 =
CALCULATE(
AVERAGE('Table'[Score]),
'Table'[Year] = "Year 2021/2022"
)
and
Variation =
DIVIDE(
STDEV.P('Table'[Score]),
AVERAGE('Table'[Score])
)
combined in a table visual
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论