Power BI 中的环比度量 [英] QoQ Measures in Power BI
问题描述
我希望在表格中进行 QoQ 计算.我面临的问题是我不知道如何计算以前的值,而当前值也显示出来.然后我会使用该字段来创建差异或百分比差异.希望下面的例子能说明问题.
I am looking to do a QoQ calculation in a table. The problem I am facing is that I do not know how to calculate the previous value while the current value also shows. I would then use that field to create a difference or % difference. Hopefully the following example makes it clear.
样本数据:
Quarter | Year |Product| Measure
Q1/19 | 2018 | A | 22
Q1/19 | 2018 | B | 61
Q1/19 | 2018 | C | 187
Q2/19 | 2018 | A | 121
Q2/19 | 2018 | B | 31
Q2/19 | 2018 | C | 15
Q3/19 | 2018 | A | 68
Q3/19 | 2018 | B | 200
Q3/19 | 2018 | C | 75
Q4/19 | 2018 | A | 123
Q4/19 | 2018 | B | 86
Q4/19 | 2018 | C | 153
Q1/19 | 2019 | A | 30
Q1/19 | 2019 | B | 131
Q1/19 | 2019 | C | 178
Q2/19 | 2019 | A | 168
Q2/19 | 2019 | B | 138
Q2/19 | 2019 | C | 87
Q3/19 | 2019 | A | 56
Q3/19 | 2019 | B | 15
Q3/19 | 2019 | C | 88
Q4/19 | 2019 | A | 96
Q4/19 | 2019 | B | 173
Q4/19 | 2019 | C | 76
期望的输出:
如下所示
Desired Output:
It would be something like below
尝试:
我根据下面的链接尝试了以下公式,但它不起作用
链接:https://community.powerbi.com/t5/Desktop/How-to-add-previous-row-value-with-current-one-and-then-keep/td-p/493467
Previous Value = CALCULATE(SUM('Sheet1'[Measure]),
FILTER(Sheet1,Sheet1[Quarter]<=EARLIER('Sheet1'[Quarter])))
我会在 Power bi 中使用 PREVIOUSQUARTER 函数,但我认为我必须将季度转换为日期,并且我需要使用的季度与日历季度不同
I would use the PREVIOUSQUARTER function in Power bi but then I believe I would have to convert quarter into date AND the quarter that I need to use is not the same as the calendar quarter
推荐答案
我要做的第一件事是尝试在您的宿舍中创建有序性.现在你只列出带有后缀/19 的季度,但我可以想象你也打算拥有/18?在这种情况下,我建议将年份放在第一位,然后是您的季度,因为这样在按字母顺序排序时会产生正确的顺序.例如.现在按字母顺序排序时,Q1/19 在 Q2/18 之前,而 18/Q2 将在 19/Q1 之前正确排序.
The first thing I'd do is try to create ordinality in your quarters. Right now you only list quarters with the post-fix /19 but I can imagine you meant to have /18 as well? In that case I would advice to put the year first and then your quarter as that will result in the right order when sorting alphabetically. E.g. Q1/19 comes before Q2/18 when sorted alphabetically now, while 18/Q2 would be sorted correctly before 19/Q1.
虽然您可以在单个度量中提出请求,但我建议您使用计算列和度量,以便于理解.
Although you can do you request in a single measure I'd recommend using a calculated column and a measure so it remains easy to understand.
首先创建一个分组索引列(计算列).这将为每个季度创建组,并按字母顺序添加索引号对它们进行排序.
First you create a grouped index column (calculated column). This will create groups for each quarter and will sort them alphabetically adding an index number.
gIndex = RANKX( SUMMARIZE( 'Sheet1' ; 'Sheet1'[Quarter] ) ; 'Sheet1'[Quarter] ; ; ASC )
然后您可以使用以下(度量)来创建时移季度总计
Then you can use the following (measure) to create the timeshifted quarter total
Previous Q =
CALCULATE (
SUM ( 'Sheet1'[Measure] ) ;
FILTER (
ALLSELECTED ( 'Sheet1' ) ;
'Sheet1'[gIndex] = MAX ( 'Sheet1'[gIndex] ) - 1
)
)
这篇关于Power BI 中的环比度量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!