Forum Discussion

stjimi's avatar
stjimi
Frequent Visitor
4 years ago
Solved

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...
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    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

     

     

OwenAuger's avatar
OwenAuger
Super User
4 years ago

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