group by - Quarterly 'Year-to-Date' Calculations in Power Query - Stack Overflow

admin管理员组

文章数量:1277335

Assume I own a business, and have the following monthly sales table:

Month	Sales Amount
01	5
02	12
03	8
04	23
05	27
06	17
07	15
08	18
09	14
10	9
11	11
12	6

Assume I own a business, and have the following monthly sales table:

Month	Sales Amount
01	5
02	12
03	8
04	23
05	27
06	17
07	15
08	18
09	14
10	9
11	11
12	6

I'd like to create a table of YTD sales amounts by quarter:

Quarter	YTD Sales Amount
Q1	25
Q2	92
Q3	139
Q4	165

How can I do this in Power Query?

I can use GroupBy to get a table of quarterly sales, but summing them all together each quarter seems more complex than GroupBy.

• group-by
• powerbi
• powerquery

Share Improve this question Follow asked Feb 24 at 16:54 GoodAnalysisGoodAnalysis 2344 bronze badges 1

• @horseyride I'm unsure about how to create that last column you mention, since the Sum is only applying to certain rows for each row. For example, the Q2 value should be [Q1 Sales] + [Q2 Sales] whereas the Q3 value should be [Q1 Sales] + [Q2 Sales] + [Q3 Sales]. – GoodAnalysis Commented Feb 24 at 19:04

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

you can try this

1. create the running total

   = Table.AddColumn(#"Changed Type", "running total", each List.Sum(Table.SelectRows(#"Changed Type",(x)=>x[Month]<=[Month])[Sales Amount]))

2. create the quarter

   = Table.AddColumn(#"Added Custom", "Q", each "Q" & Text.From(Number.RoundUp( Number.From([Month])/3)))

3. only keep month for 3, 6, 9,12

   = Table.SelectRows(#"Added Custom1", each ([Month] = "03" or [Month] = "06" or [Month] = "09" or [Month] = "12"))

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc27EQAgCAPQXagtBP+zeOy/honQ5F4RyL1SVYoM8QIaqBZu8A520Fp40Cs8WU8vOt9sOm8P3b+1wifIUcQ3V6e4Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, #"Sales Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "running total", each List.Sum(Table.SelectRows(#"Changed Type",(x)=>x[Month]<=[Month])[Sales Amount])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Q", each "Q" & Text.From(Number.RoundUp( Number.From([Month])/3))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Month] = "03" or [Month] = "06" or [Month] = "09" or [Month] = "12"))
in
    #"Filtered Rows"

Try this:
Change the Source line to your actual Source

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc27EQAgCAPQXagtBP+zeOy/honQ5F4RyL1SVYoM8QIaqBZu8A520Fp40Cs8WU8vOt9sOm8P3b+1wifIUcQ3V6e4Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, #"Sales Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Sales Amount", Int64.Type}}),

//Group by quarters
// Note we use GroupKind.Local
//    Assumes months are in sequential order by date
//    If there is more than one year, the Quarters for the different years will be grouped separately
    #"Grouped Rows" = Table.Group(#"Changed Type", "Month", {
        {"Quarterly Sales", each List.Sum([Sales Amount]), type nullable number}},
        GroupKind.Local,(x,y)=>Number.IntegerDivide(x-1,3) - Number.IntegerDivide(y-1,3)),
    
//Compute running total and add it as a column
    #"Add Running Total" = 
        Table.FromColumns(
            Table.ToColumns(#"Grouped Rows") & 
            {List.Generate(
                ()=>[rt=#"Grouped Rows"[Quarterly Sales]{0}, idx = 0],
                each [idx] < Table.RowCount(#"Grouped Rows"),
                each [rt=[rt] + #"Grouped Rows"[Quarterly Sales]{[idx]+1}, idx=[idx]+1],
                each [rt])}, type table[Month=text, Sales Amount=Currency.Type, YTD Sales Amount=Currency.Type]),
    
//Don't need this in the final result
    #"Removed Columns" = Table.RemoveColumns(#"Add Running Total",{"Sales Amount"}),
    
//Rename the "Month" column and transform the month numbers to Quarters
    #"Rename Month" = Table.RenameColumns(#"Removed Columns",{"Month","Quarter"}),
    #"Month to Quarter" = Table.TransformColumns(#"Rename Month",
        {"Quarter", each Number.ToText(Number.IntegerDivide(_-1,3)+1, """Q""0"), type text})
    
in
    #"Month to Quarter"

本文标签： group byQuarterly 39YeartoDate39 Calculations in Power QueryStack Overflow