计算tableau中每一行的价格差异 [英] Calculate price difference for each row in tableau

问题描述

我有一个返回数据的报告，如

I have a report that returns the data like

项目 |价格 |区别

A |1.00 |0

B |2.00 |1.00

B | 2.00 | 1.00

C |0.50 |-0.50

C | 0.50 | -0.50

我有一个允许选择项目的参数，我想知道如何编写根据用户选择重新计算的差异公式:即，如果使用将选择项目 B，那么所有价格都应计算为两者之间的差异当前行的价格和项目 B 的价格.

I have a parameter that allows to select Item, I wonder how can I write up formula for the Difference that would recalculate based on user selection: i.e if use would select item B than all the price should be calculated as difference between the price for current row and the price for item B.

如果用户选择项目 B 的预期输出

Expected output if user would select item B

A |1.00 |-1.00

A | 1.00 | -1.00

B |2.00 |0.00

B | 2.00 | 0.00

C |0.50 |-1.50

C | 0.50 | -1.50

推荐答案

这个问题很难搞清楚，但我明白了:

This was a bastard to figure out, but I have it:

假设您已经有了参数，我们需要的是一个具有逻辑的字段:

Assuming you've already got your parameter, what we need is a field that has the logic:

如果类别与参数中选择的相同，则获取价格"

"If category is the same as what's selected in the parameter, then get the price"

这里 F1 是您的类别，F1 参数是基于此字段的参数

Here F1 is your category and F1 parameter is the parameter based off of this field

"Get parameter select price" = IF [F1 Parameter] = [F1] THEN [Price] END

然后我们想在每一行中放置这个字段的值(这样我们就可以计算它和类别价格之间的差异)

We then want to place the value for this field in every row (so we can take the difference between it and the category's price)

为此，我们使用了 FIXED 计算和 MAX.由于 IF 语句只输出参数选择行的价格，其余为空，使用 MAX 将把这个值放在每一行:

To do this we used the FIXED calculation and MAX. Since the IF statement only outputs the price for the parameter selected row, with the rest being null, using MAX will put this value in every row:

"Parameter Selected Value for all rows" =    {FIXED: MAX(IF [F1 Parameter] = [F1] THEN [Price] END)}

然后你可以做的区别:

"Difference" = [Price] - [Parameter Selected Value for all rows]

这是一个表格来演示:

如果您需要，很乐意进一步解释

Happy to explain further if you need

如果您希望所选类别的差异列中有一个空值，因为它始终为零，您可以将差异"更改为:

If you want to have a null value in the difference column for the selected category, since it'll always be zero, you can change "Difference" to:

IF [F1 Parameter] = [F1] THEN NULL ELSE Price - [Parameter Selected Value for all rows] END

图片显示:

这篇关于计算tableau中每一行的价格差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！