Need help - Dynamic Average Calculated Measure

[Anonymous]

Hi all,

I have data in PowerBI as shown below in column A,B and C.

I want to create a calculated measure which gives the average value by objective and by month.

For example, if I filter month Feb19 and Mar19 then the [Avg by objective] should show number '2.5' and '5.5' for objective A and B, respectively (As shown in the second table).

Currently, my [Avg by objective] always shows the same value (the average of value by objective) and did not change when I filter the month.

Avg by objective = CALCULATE(AVERAGE('Table'[Value]), ALLEXCEPT(Table, 'Table[Objective]))

How could I achieve this? Any help would be appreciated. Thank you.

[TeigeGao · Accepted solution]

Hi @Anonymous ,

Please refer to the following DAX query:

Avg by objective =
VAR selectedvalues =
    ALLSELECTED ( 'Table'[Month] )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Objective] = MIN ( 'Table'[Objective] )
                && 'Table'[Month] IN selectedvalues
        )
    )

The result will like below:

Best Regards,

Teige

[Anonymous]

@TeigeGao Many thanks !!