Forum Discussion

Irisohyama6's avatar
Irisohyama6
Frequent Visitor
4 years ago
Solved

Moving Average

Hello all,   I am trying to get moving average for my data but I got stuck and wondering if any veteran know how to solve this.   Here are my dummy tables: Top left and right are the table...
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Irisohyama6 

    Since you are involved in iterative recursion, using DAX is not ideal. Power Query is recommended.
    Such as:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLLTM1JAdJ++UqxOhAhr9Ti4szkRCArMrUYIZqfhyYSnJuYk4Mk5gRku6QW5+bnpaCJolnihGGcExbjnFGMg2p1xjTNGauTneF2IClDsSIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Event = _t, Name = _t, Attendance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"Name", type text}, {"Attendance", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Event"}, {{"Seats", each Table.RowCount(_), Int64.Type}, {"a", each _, type table [Event=nullable text, Name=nullable text, Attendance=nullable text, #"Table (2).Target Occupation"=nullable number]}}),
    Custom1 = Table.AddColumn(#"Grouped Rows", "Atten", each Table.SelectRows([a], each ([Attendance] = "Yes"))),
    Custom2 = Table.AggregateTableColumn(#"Custom1", "Atten", {{"Event", List.Count, "Atten"}}),
    #"Added Custom" = Table.AddColumn(Custom2, "Occupation", each [Atten]/[Seats]),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Event"}, #"Table (2)", {"Event"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Target Occupation"}, {"Target Occupation"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table (2)",{{"Occupation", Percentage.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Target", each [Occupation]/[Target Occupation]),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 1, 1, Int64.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Index", "Average", each List.Accumulate(
    List.FirstN( #"Added Index"[Target],[Index]
),List.First(  #"Added Index"[Target]),(x, y) => (x + y)/2)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Index"})
in
    #"Removed Columns"

    The final output is shown below:

     


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
4 years ago

Irisohyama6 , You need a measure like

 

calculate( AverageX(Values(Event[Event]), Calculate(Sum('Target'[Target Occupation]))), filter(allselected(Event), Event[Event]<=max(Event[Event])))

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Irisohyama6's avatar
Irisohyama6
Frequent Visitor
4 years ago

Thanks for the reply.

It does not work maybe because of the reference field. Here is the error:

Here are the tables. I entered the Dax you provided as 'testing'.

 

I cannot attach excel of pbix file here so I will enter data here.

Table:

EventNameAttendance
AFieldNo
AJessicaYes
AJonYes
ASmallYes
BDesmondYes
BFieldNo
BJonYes
BSmallYes
CDesmondNo
CFieldNo
CJessicaYes
CJonNo
CSmallYes

 

Target:

EventTarget Occupation
A0.6
B0.75
C0.9

 

Thank you very much for you time.

 

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Irisohyama6 , I was assuming a common event table across two tables , you can use the correct table name for event, based on what has been used in display /visual

    • Irisohyama6's avatar
      Irisohyama6
      Frequent Visitor
      4 years ago

      Thanks for your reply.

       

      I changed to the correct table that was used in the table visual. The measure somehow is not working yet.