PowerBI：如何计算矩阵中两列之间的差异 [英] PowerBI: how do you calculate the difference between two columns in matrix

问题描述

我有一个定义为值的矩阵：

I have a matrix defined as the values :

我想运行基线和方案1在每个日期上看到的值之间的差异（可以大于2）
因此，列定义为：
（1）日期
（2）类型

I wanted to run a difference between the values that is seen on each date from Baseline and Scenario1 (it can be more than 2) So the columns are defined as : (1) Date (2) Type

因此，每个日期与基准的差异与我们在计算中所做的一样-Excel或Tablaeu中的差异

So the difference is from Baseline for each Date much as what we do in Calculation - Difference in Excel or Tablaeu

有人有我可以参考的材料吗？
我有以下DAX，但它没有给我我期望的结果：

Does anybody have any materials where I can follow through? I have the following DAX but it does not give me the result I expect :

Diff = 
VAR currIndexDate = MIN (Date [TotalRundate]) 
VAR currIndexType = "Scenario1" 

var currVolume = 
CALCULATE ( SUM (Data[TotalVolume]), 
      FILTER( ALLSELECTED (Data) , Data[TotalRunDate] = currIndexDate && Date[Type] = currIndexType ),
      VALUES (Data[Type]), VALUES (Data[TotalRunDate])
)
var prevVolume = 
CALCULATE ( SUM (Data[TotalVolume]), 
      FILTER( ALLSELECTED (Data) , Data[TotalRunDate] = currIndexDate && Date[Type] <> currIndexType ),
      VALUES (Data[Type]), VALUES (Data[TotalRunDate])
)
RETURN 
IF (prevVolume <> BLANK , currVolume - prevVolume, currVolume)

这是我得到的结果，它对所有内容求和而不是在同一Data [Type]组中数据[TotalRunDate]

Here is the result that I am getting which sums everything and not within the same grouping of Data[Type] Data[TotalRunDate]

我想要完成的就是这种类型：（这是Excel中的差值计算）

What I want to accomplish is this type: (which is the Difference Calculation in Excel)

推荐答案

我的理解：显示列标题Type和Type = Baseline之间的区别，而不影响任何

My understanding: Show difference between column-header Type and Type=Baseline, without affecting any other context.

Volume = SUM ( 'Data'[TotalVolume] )

Diff from Baseline =
[Volume] - CALCULATE ( [Volume], 'Data'[Type] = "Baseline" )

您无需执行任何特殊操作即可保留过滤上下文。只需使用 CALCULATE 即可更改您关心的上下文。

You don't need to do anything special to preserve filtering context. Just use CALCULATE to change the bit of context you care about.

这篇关于PowerBI：如何计算矩阵中两列之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！