Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
2 years ago
Solved

calculating by distinct date

I am trying to add the totals from each store and then divide by the unique # of dates provided (as some stores will not have all the dates).  How could I achieve this in within Power Query?  I understand I would have to do some grouping by store number, but not sure how to do the calculation by theunique # of dates.  

Current  Desired
DateStore NumberTotals  Store NumberAvg Total by Unique # of Dates
1/1/20221815  1860 (***420/7***)
1/2/20221830  2042.5 (***170/4***)
1/3/20221845  3130.5 (***61/2***)
1/4/20221860    
1/5/20221870    
1/6/202218100    
1/7/202218100    
1/1/20222050    
1/2/20222050    
1/3/20222050    
1/4/20222020    
1/1/20223111    
1/2/20223150    
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    Change the Source line to reflect your actual data source:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Store Number", Int64.Type}, {"Totals", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store Number"}, {
        {"Average Sales per Day", each List.Sum([Totals]) / List.Count(List.Distinct([Date])), type number}})
in
    #"Grouped Rows"

     

     

     

1 Reply

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    Change the Source line to reflect your actual data source:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Store Number", Int64.Type}, {"Totals", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store Number"}, {
        {"Average Sales per Day", each List.Sum([Totals]) / List.Count(List.Distinct([Date])), type number}})
in
    #"Grouped Rows"