Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

Reply
IHam
Helper III
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:

subjectsittingmark"average"
mathssummer 2023 60(70+80+90+50)/4
frenchsummer 2023 70(60+80+90+50)/4
englishsummer 2023 80(60+70+90+50)/4
biologysummer 2023 90(60+70+80+50)/4
historysummer 2023 50(60+70+80+90)/4
1 ACCEPTED SOLUTION
Dangar332
Super User
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)

 

Dangar332_0-1698828629299.png

 

View solution in original post

3 REPLIES 3
IHam
Helper III
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.

 

ThxAlot
Super User
Super User

ThxAlot_0-1698829241857.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



Dangar332
Super User
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)

 

Dangar332_0-1698828629299.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.