cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bazari
Frequent Visitor

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.
 

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

@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"

Screenshot 2021-03-26 093948.png

bazari
Frequent Visitor

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.?

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors