cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

Example2.PNG

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
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:

YJSsnHbF8o.gif

Best Regards,

Teige

View solution in original post

2 REPLIES 2
TeigeGao
Solution Sage
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:

YJSsnHbF8o.gif

Best Regards,

Teige

View solution in original post

Anonymous
Not applicable

@TeigeGao Many thanks !!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

Get Ready for Power BI Dev Camp

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