cancel
Showing results for
Did you mean:
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 Number Jurisdiction Average Average of others Control 1 SA 2 3.5 Control 1 NSW 3 3 Control 1 WA 4 2.5 Control 2 SA 3 3.5 Control 2 NSW 4 3 Control 2 WA 3 3.5 Control 3 SA 7 5.5 Control 3 NSW 6 6 Control 3 WA 5 6.5
1 ACCEPTED SOLUTION
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

My Blog
3 REPLIES 3
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.

Super User

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

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

My Blog

Announcements

#### 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.