QoQ计算功率BI

Question

我在下面的数据表中计算QoQ Imp和QoQ %Eng，该数据表通过添加“索引从0开始”和“索引1从1开始”而按power查询进行分组。

​

​

我在过滤器窗格中有一个“过滤器”列这个可视的。请帮助我计算上表A中的QoQ Imp和QoQ %Eng。预期的结果/输出应如下表所示：-

​

Answer 1

在Power查询(M代码)中，假设数据具有代表性，您可以在筛选/分组等之前计算QoQ值。

• 在显示
• 时添加索引列，然后添加一个Modulo列(value = 4，因为您有4个季度)
• 然后为您的两个计算添加自定义列：

​

​

​

• 然后根据需要过滤

下面是假设数据源是Excel表的M代码。根据需要修改源行

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Qtr", type text}, {"Filter", type text}, {"Imp", Int64.Type}, {"Eng", Int64.Type}}),

//Compute % Eng column
    #"Added Custom" = Table.AddColumn(#"Changed Type", "% Eng", each [Eng]/[Imp], Percentage.Type),

//Add Index and Modulo columns
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 4), type number),
 
 //Compute QOQs -- (current row - previous row)/previous row (unless first row in the group in which case => null
    #"Added Custom1" = Table.AddColumn(#"Inserted Modulo", "QoQ Imp", each if [Modulo]=0 then null
        else ([Imp] - #"Inserted Modulo"[Imp]{[Index]-1}) / #"Inserted Modulo"[Imp]{[Index]-1}),
 
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "QoQ %Eng", each if [Modulo]=0 then null
        else ([#"% Eng"] - #"Inserted Modulo"[#"% Eng"]{[Index]-1}) / #"Inserted Modulo"[#"% Eng"]{[Index]-1}),

//remove now superfluous Index and Modulo columns and re-order the others
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index", "Modulo"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Qtr", "Filter", "Imp", "QoQ Imp", "Eng", "% Eng", "QoQ %Eng"})
in
    #"Reordered Columns"

​

​