cancel
Showing results for
Search instead for
Did you mean:
Anonymous
Not applicable

## Need help - Dynamic Average Calculated Measure

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.

1 ACCEPTED SOLUTION
Solution Sage

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

2 REPLIES 2
Solution Sage

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
Not applicable

@TeigeGao Many thanks !!

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors