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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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!