Calculate average of calculated measure across all dimensions

[dpc_development]

Please see this uploaded Power BI file. With help from @OwenAuger I have a calculated measure called 'cumulative_return'. I am then calculating another measure called '7d_cumulative_return', which is basically the cumulative_return as on seven days prior to the current last_updated date.

Now, I need to calculate the average of 7d_cumulative_return across all lists. You will notice that the values for list 2 & 1027 on the 3rd is 1.051 and 1.085, and on 4th is 0.906 and 0.923, respectively. Thus my new measure should return 1.068 and 0.9145, respectively.

This is the formula that I have for now.

avg_7d_cumulative_return = 
var onDate = MAX('CMC Daily Return'[last_updated])
RETURN 
CALCULATE(
    AVERAGEX(ALLSELECTED('CMC Daily Return'), [7d_cumulative_return]),
    'CMC Daily Return'[last_updated] <= onDate,
    ALLSELECTED('CMC Daily Return')
)

It starts well, but shows a wrong value from the second day onward. How can I get the average of a measure that depends on a dimension, but I need the average across all dimension values.

[OwenAuger · Accepted solution]

Hi there @dpc_development 

To calculate the average of the existing measure [7d_cumulative_return] over the list_id, you can use a measure like below.

This assumes you want to show the same average across all selected list_id values.

avg_7d_cumulative_return = 
AVERAGEX ( 
    ALLSELECTED ( 'CMC Daily Return'[list_id] ),
    [7d_cumulative_return]
)

Since all the logic around date filters is captured in the underlying measures, you don't need to rewrite any date filters in this measure.

Regards,

Owen