Excel Dax函数计算销售额市场份额

在这里我们使用与SQL语句计算销售额市场份额文章中相同的业务场景来展示如何在excel中使用Dax函数来计算销售额市场份额。

业务场景：某零售商想要分析每月各个省份各个渠道维度下品牌的销售额市场份额。

品牌销售额市场份额=品牌销售额/品类总销售额

我们使用同样的sales表数据，如下图所示

如图所示我们已经将sales数据通过从文本/CSV方式加载到了PowerPivot模型中去了（详情请参考Excel Power Pivot 新手入门文章），现在我们开始创建数据透视表并创建度量值指标计算品牌的销售额市场份额。

1. 从模型中创建数据透视表

1). 打开PowerPivot中的数据模型管理

2). 选择已加载的sales数据表点击创建数据透视表图标选择第一个创建数据透视表

3). 选择新工作表/现有工作表确定即可

4). 选择刚刚创建的数据透视表点击鼠标右键选择显示字段列表

5). 如图所示，在此可以选择需要分析的数据维度

2. 创建度量值指标

1). 选择PowerPivot，点击度量值，选择新建度量值  

2). 表名sales，度量值名称amt_brand，在公式输入框输入公式，这里我们使用sum聚合函数来计算品牌的销售额，公式=sum([amount])，或者=sum(sales[amount])。

语法：sum(列名/表名[列名])

3). 同上2，我们创建另一个度量值amt_total来计算品类总销售额，公式=CALCULATE(sum([amount]),ALL(sales[brand_name]))或者=CALCULATE(sum([amount]),ALLEXCEPT(sales,sales[month],sales[province_name],sales[channel],sales[category]))。

这里我们用到了三个Dax的筛选器函数，CALCULATE()，ALL()和ALLEXCEPT()。

语法1：CALCULATE(表达式,[筛选条件])，它修改了应用于返回表对象的表达式的筛选器上下文。

        表达式一般为聚合函数，例如SUM()，AVERAGE()，MAX()等。

        筛选条件可以为0到多个，一般为筛选器函数，例如我们公式中的ALL()和ALLEXCEPT()。

语法2：ALL(表名|列名)，返回表中的所有行或列中的所有值，例如公式中返回所有品牌的销售额，即品牌筛选器在计算销售额之和时不起作用，被忽略掉了。

        注意表名和列名不可同时使用。

语法3：ALLEXCEPT(表名,列名)，返回表中除指定列之外的列的所有值，返回除了month、province_name、channel、category之外的列的所有值，即所有品牌的销售额之和。

4). 我们已经计算好了销售额市场份额的分子品牌销售额amt_brand和分母amt_total，现在我们创建销售额份额的度量值market_share，方法同2，公式=DIVIDE([amt_brand],[amt_total],0)。

语法：DIVIDE(分子，分母，[<alternateresult>])，当分母不为0时执行除法运算返回结果，当分母为0 时返回指定的<alternateresult>结果或 BLANK()。例如公式中我们设置当被0除时返回0。

5). 我们可以合并2、3、4直接计算market_share结果。

公式=DIVIDE(sum([amount]),CALCULATE(sum([amount]),ALL(sales[brand_name])),0)

或者

=DIVIDE(sum([amount]),CALCULATE(sum([amount]),ALLEXCEPT(sales,sales[month],sales[province_name],sales[channel],sales[category])),0)

6). 我们将创建好的度量值指标添加到数据透视表的值中如图6.1。数据透视表如图6.2所示，分母amt_total为所有品牌销售额之和，农夫山泉的销售额市场份额为55%，与我们使用SQL计算的结果一致。

注：图中只显示了部分品牌销售额。

更多dax函数学习，请参考数据分析表达式 (DAX) 引用 - DAX | Microsoft Learn