Forum Discussion

bazari's avatar
bazari
Frequent Visitor
5 years ago

week average on missing values

Hi, I have a Table with Missing values on weekends and holidays,
I need to populate missing values with the weekly average.
I have used Simple Formula 

IF(
    ISBLANK(sales[KPMR]),
    AVERAGE(sales[KPMR]),
    sales[KPMR]
   )

but the average is for the whole period, not the row week I need. I have a similar dataset written down.


dateWeekdayweek numValues (KPMR)
10/11/2020742 
10/12/202014211
10/13/202024284
10/14/202034242
10/15/202044295
10/16/202054242
10/17/2020642 
10/18/2020743 
10/19/202014318
10/20/202024324
10/21/202034317
10/22/202044347
10/23/202054382
10/24/2020643 
10/25/2020744 


how can I Populate missing values based on the weekly average or monthly average?
I have multiple rows per day (let's say transactions), but the Value (KPMR) will be the same.
 

DAX
values
weekly-averages

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    bazari , you might want to achieve your goal in Power Query,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9BDoUgDIThq7ywNpEORfAsxvtfQ9BMzcS3oJuPJv2PI1lezVZk5LSkNp5jjF86l8dAM5pZYCGC2D3QiYU4BrESnbjXwI1Y/2w24vY9tktIEdslZJp1IrKETESEwCTk3myBkJD7x4tFQib2CIFLiByLKiH+2HkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, Weekday = _t, #"week num" = _t, #"Values (KPMR)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"Weekday", Int64.Type}, {"week num", Int64.Type}, {"Values (KPMR)", Int64.Type}}),
    #"Grouped Rows" = Table.RemoveColumns(Table.Group(#"Changed Type", {"week num"}, {{"ar", each _, type table [date=nullable date, Weekday=nullable number, week num=nullable number, #"Values (KPMR)"=nullable number]}}), {"week num"}),
    Population = Table.TransformColumns(
        #"Grouped Rows", 
        {"ar", 
        each let kpmr = [#"Values (KPMR)"], avg = List.Sum(kpmr)/List.NonNullCount(kpmr) in Table.ReplaceValue(
            _, null, avg, Replacer.ReplaceValue, {"Values (KPMR)"}
        )}
    ),
    #"Expanded ar" = Table.ExpandTableColumn(Population, "ar", {"date", "Weekday", "week num", "Values (KPMR)"}, {"date", "Weekday", "week num", "Values (KPMR)"})
in
    #"Expanded ar"

    • bazari's avatar
      bazari
      Frequent Visitor
      5 years ago

      So, no Simple DAX can resolve that? that's the quite complicated solution. I Have to apply that on 3 million rows, so i think it will be quite slow.?