cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stjimi
Frequent Visitor

Average everything else

HI All, 

I am new to Dax and I can't seem to figure out how to find the average of all values in the category without including the present row of another category. For example, I want to find the average of all controls of all other jurisdictions except the one on the row. See below. 

Sorry, I can't explain it properly. 

 

Control NumberJurisdictionAverageAverage of others 
Control 1 SA23.5
Control 1NSW33
Control 1 WA42.5
Control 2SA33.5
Control 2NSW43
Control 2WA33.5
Control 3SA75.5
Control 3NSW66
Control 3WA56.5
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @stjimi 

From your example, it looks like a measure such as this would give the result you want:

 

Average of others = 
CALCULATE ( 
    [Average],
    EXCEPT ( 
        ALLSELECTED ( YourTable[Jurisdiction] ),
        VALUES ( YourTable[Jurisdiction] )
    )
)

 

This assumes a measure [Average] is already defined.

The EXCEPT ( ... ) expression creates a modified filter on Jurisdiction, by taking the filter on Jurisdiction applying to the overall visual, and removing the value(s) of Jurisdiction visible in the current filter context, i.e. removes the single value from the current row of your table in your example.

 

Regards,

Owen

 

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

3 REPLIES 3
stjimi
Frequent Visitor

Thanks so much, Owen. This makes sense.  How would I go about defining a measure for the Average? Would I do this with a 'new measure'. Sorry about the simple question. 

It would just be AVERAGE ( Table1[Column1] ) where you substitute whatever column you're wanting to average instead of my placeholder.

OwenAuger
Super User
Super User

Hi @stjimi 

From your example, it looks like a measure such as this would give the result you want:

 

Average of others = 
CALCULATE ( 
    [Average],
    EXCEPT ( 
        ALLSELECTED ( YourTable[Jurisdiction] ),
        VALUES ( YourTable[Jurisdiction] )
    )
)

 

This assumes a measure [Average] is already defined.

The EXCEPT ( ... ) expression creates a modified filter on Jurisdiction, by taking the filter on Jurisdiction applying to the overall visual, and removing the value(s) of Jurisdiction visible in the current filter context, i.e. removes the single value from the current row of your table in your example.

 

Regards,

Owen

 

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors
Top Kudoed Authors