Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

averages

Dear Community,

I need to calculate the average for three specific product (#1,#2,#3) costs, on a daily basis. Then that average should appear as "product 5". I don't know what should be the proper approach to do that.

 

DateProductCost
1-1-202014
1-1-202023
1-1-202035
1-1-202041
5-1-202011
5-1-202022
5-1-202033
5-1-202046

 

Outcome:

DateProductCost
1-1-202014
1-1-202023
1-1-202035
1-1-202041
1-1-202054
5-1-202011
5-1-202022
5-1-202033
5-1-202046
5-1-202052

 

Thanks in advance.

Cheers

  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Hi, Anonymous , a simple measure + matrix viz do the trick with ease

    AVG 123 = 
AVERAGEX (
    FILTER ( DISTINCT ( 'Table'[Product] ), 'Table'[Product] IN { 1, 2, 3 } ),
    CALCULATE ( SUM ( 'Table'[Cost] ) )
)

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  Note that two steps of the same query are appended to get your desired result.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ11DUyMDJQ0lEyBGITpVgdFEEjIDZGFzQGYlN0QROwESBBU1QzMQSNwBhN0BhukSmqmWZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Cost = _t]),
    InitialTable = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", Int64.Type}, {"Cost", Int64.Type}}),
    Custom1 = InitialTable,
    #"Filtered Rows" = Table.SelectRows(Custom1, each ([Product] <> 4)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date"}, {{"Product", each List.Min([Product]), type nullable number}, {"Cost", each List.Average([Cost]), type nullable number}}),
    TableToAppend = Table.ReplaceValue(#"Grouped Rows",1,5,Replacer.ReplaceValue,{"Product"}),
    #"Appended Query" = Table.Combine({InitialTable, TableToAppend}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

     

    Regards,

    Pat

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Hi, Anonymous , a simple measure + matrix viz do the trick with ease

    AVG 123 = 
AVERAGEX (
    FILTER ( DISTINCT ( 'Table'[Product] ), 'Table'[Product] IN { 1, 2, 3 } ),
    CALCULATE ( SUM ( 'Table'[Cost] ) )
)