Power BI：百分比变化公式 [英] Power BI: Percent Change Formula

问题描述

我想在Power BI中创建一个简单的百分比变化公式。具体来说，我想评估费用的逐月变化。我知道可以用不同的方式来定义百分比变化，因此要明确地说，这就是我所指的公式：

  ％change =（[当前值]-[先前值]）/ [先前值]。 
  

以下是我要实现的Excel示例：

<我找不到在DAX公式中定位特定行的方法。看来Power BI只想包含诸如总和，最大值之类的合计值。

最后一点，我知道PowerBI包含一个百分比变化快速度量 。但是，基本价值始终要求您选择汇总指标，而不是单个行值。

解决方案

如Alexis所述，在DAX中没有引用特定行的概念。对于习惯使用Excel的人来说，这可能是一个很难的概念，但是如果您想利用PowerBi，则必须取消学习 Excel的思维方式。

要实现您的目标，您需要首先进行一些数据建模。通常，将日期列替换为适当的日历表：

一旦有了这样的结构，DAX很简单：

 本月费用= SUM（费用[费用]）
 
上个月费用=计算（[[本月费用] ，PREVIOUSMONTH（Calendar [Date]））
 
％更改=除（[当前月成本]-[上月成本]，[前月成本]）
  

I want to create a simple percent change formula in power BI. Specifically, I want to evaluate month over month %change in cost. I know that percent change can be defined in different ways, so to be clear this is the formula that I am referring to:

%change = ([current value] - [previous value])/[previous value].

Here is an example in Excel of what I want to achieve:

I cannot find a way to target a specific row in a DAX formula. It seems that power BI only wants to include aggregate values such as sum, max, etc.

As a final note, I know that PowerBI includes a percent change "quick measure". However, the 'Base Value' always requires you to select aggregate measure instead of a single row value.

解决方案

As Alexis mentioned, there is no concept of referring to a specific row in DAX. It can be a hard notion for people who are used to Excel, but if you want to take advantage of PowerBi, you will have to "unlearn" Excel way of thinking.

To accomplish your goal, you need to do some data modeling first. As a common practice, replace your "Date" column with a proper Calendar table:

Calendar table explained

As a result, you will have a data model in PowerBi that looks something like this:

Once you have such structure in place, DAX is simple:

Current Month Cost = SUM(Expenses[Cost])

Previous Month Cost = CALCULATE( [Current Month Cost], PREVIOUSMONTH(Calendar[Date]))

% Change = DIVIDE( [Current Month Cost] - [Previous Month Cost], [Previous Month Cost])

这篇关于Power BI：百分比变化公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！