cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## calculate average but don't include the value

I am trying to calculate a mean for an examination sitting but finding the average of values not including the value itself in dax.

Any help would be great.

To illustrate:

 subject sitting mark "average" maths summer 2023 60 (70+80+90+50)/4 french summer 2023 70 (60+80+90+50)/4 english summer 2023 80 (60+70+90+50)/4 biology summer 2023 90 (60+70+80+50)/4 history summer 2023 50 (60+70+80+90)/4
1 ACCEPTED SOLUTION
Super User

HI, @IHam

``````Measure 2 =
var a = CALCULATE(SUM(sub[mark]),sub[subject]<>MAX(sub[subject]))
var b =CALCULATE(DISTINCTCOUNT(sub[subject]),sub[subject]<>MAX(sub[subject]))
return
DIVIDE(a,b)``````

3 REPLIES 3
Helper III

Many thanks to both of you - they both work but the second solution is the one that seems to match my set-up.

Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

HI, @IHam

``````Measure 2 =
var a = CALCULATE(SUM(sub[mark]),sub[subject]<>MAX(sub[subject]))
var b =CALCULATE(DISTINCTCOUNT(sub[subject]),sub[subject]<>MAX(sub[subject]))
return
DIVIDE(a,b)``````

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors