Forum Discussion

jct999's avatar
jct999
Advocate II
5 years ago
Solved

Complex calculation with 2 steps : Sum then average.

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 ...
jct999's avatar
jct999
Advocate II
5 years ago

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's avatar
    wdx223_Daniel
    Community Champion
    5 years ago

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