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 tables, bottom is another table includes a lot of measures. Both tables on the top are linked by 'Event'.  Let me explain the bottom table and what I want to achieve.

 

I did it step by step.

Attendee = COUNTROWS(FILTER('Table','Table'[Attendance]="Yes")) --- to count the 'Yes'

Seats  = COUNTROWS('Table') --- count the total seats 

Occupation = Attendee/Seats

Target Occupation = 'Target'[Target Occupation] --- I just tick the box from 'Target' table

Target = [Occupation]/AVERAGE(Target[Target Occupation]) --- I understand I used 'Average' here. Since there is only one value per row, either SUM/AVERAGE got me the same result.

 

Now the question is, how do I get the moving average from 'Target' Column? It should look something like this:

second row is (1.25+1.00)/2 = 1.13,

third row is (1.13+0.44)/2 = 0.78

 

Thanks in advance.

 

 

 

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

5 Replies

  • 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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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.