Power BI中的QoQ措施 [英] 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 -当前值一并保存/ 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中的QoQ措施的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！