使用DataModel(内置于Excel 2013和更高版本中),您可以在数据透视表中编写称为“度量”的非常酷的公式,可以执行此类操作。请看下面的例子:
如您所见,“客户计数和平均值”字段使用以下DAX公式提供了按月进行的交易计数,还提供了小计行(即2017年总计和2018年总计)的月度读数平均值:
=AVERAGEX(SUMMARIZE(Table1,[Customer (Month)],"x",COUNTA(Table1[Customer])),[x])
That just says "Summarize this table by count of the customer field by month, call the resulting summarization field 'x', and then give me the average of that field x".
Because DAX measures are executed within the context of the PivotTable, you get the count that you want for months, and you get the average that you want for the yearly subtotals.
Hard to explain, but demonstrates that DAX can certainly do this for you.
See my answer at the following link for an example of how to add data to the DataModel and how to subsequently write measures:
Using the Excel SMALL function with filtering criteria AND ignoring zeros
我还建议您在学习Matt Mattington编写的DAX时,读一本名为Supercharge Excel的书,甚至还可以参加他的很棒的在线课程,因为它很好地涵盖了这类内容,与实际操作相比,它可以省去很多麻烦一个人。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句