Complex calculation with 2 steps : Sum then average.

[jct999]

Hello,

I have a dataset that contains Sales data.  It has the following columns :

• Period_id (integer : 1, 2, 3,... 250, 251, ... etc)
•  Country_id
•  Product_id
•  Color_id
•  Category_id
•  Quantity

I want set up a table widget with 2 columns :

•  Col #1 : Period_Id
•  Col #2 : Complex calculation :

  Step 1) Calculate the sum of quantities per Period_id, for each Period_id starting from (Period_id - 4) to Period_id of the current row.
  Step 2) Then calculate the average of the 4 sums previously calculated.

Obviously, the dataset can be also filtered with slicers on Country_id, Product_id, ...

Could you help me to write the DAX formula to perform this complex calculation ?

Thanks !

Best Regards

[wdx223_Daniel · Accepted solution]

@jct999 

[jct999]

THanks wdx223_Daniel for you reply.

I have a question in order to understand deeper :

if I refer to the DAX documentation, AVERAGEX accept, as a 1st argument, a table or an expression that specifies the table over which the aggregation can be performed.

Your 1st argument for AVERAGEX if a TOPN function : TOPN(4, FILTER(...)).
This TOPN function return a table with a single column : 'Period'[Period_id] ; and it contains no column with Quantities.

So my question is : How can AVERAGEX function extract the average from this table that has only one column and no quantity values / scalar values ?

Regards

[wdx223_Daniel]

@jct999 this about context transition

when you refer a measure in the 2nd parameter of a iterator, the context transition happens. it will transform the row context brought in by 1st parameter of the iterator into filter context and then use it to calculate the measure.

that is the AVGERAGEX generate a row context of period id, and tranform it to filter context, then used it in the calculation of [QTY Total].