Moving Average

[Irisohyama6]

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 · Accepted solution]

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]

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

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:

Event	Name	Attendance
A	Field	No
A	Jessica	Yes
A	Jon	Yes
A	Small	Yes
B	Desmond	Yes
B	Field	No
B	Jon	Yes
B	Small	Yes
C	Desmond	No
C	Field	No
C	Jessica	Yes
C	Jon	No
C	Small	Yes

Target:

Event	Target Occupation
A	0.6
B	0.75
C	0.9

Thank you very much for you time.

[amitchandak]

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

Thanks for your reply.

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