AVEARAGEX BUT FILTER OUT A SINGLE VALUE

[Dave-ExpSC]

Still learning...

I have data from multiple companies and I want to do an average across the companies but I want to exclude 1 company from this average. I used a Quick Measure to calculate the average values that I am looking for but how can I modify it to exclude company "xyz"?

Avg Intensity SC1+2 by Revenue =

AVERAGEX(

    KEEPFILTERS(VALUES('2_Company_tbl'[Company])),

    CALCULATE(SUM('3a_GHG_Intensity SC1+2 Revenue'[Value]))

)

Your help is greatly appreciated

[VahidDM]

Hi @Dave-ExpSC 

Try this:

Avg Intensity SC1+2 by Revenue =
AVERAGEX (
    CALCULATETABLE (
        KEEPFILTERS ( VALUES ( '2_Company_tbl'[Company] ) ),
        FILTER ( '2_Company_tbl', '2_Company_tbl'[Company] <> "xyz" )
    ),
    CALCULATE ( SUM ( '3a_GHG_Intensity SC1+2 Revenue'[Value] ) )
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Dave-ExpSC]

Thank you @VahidDM 

I'm getting an error message. I didn't metntion that I have a special character in front of the company name to help it sort to the top of company names "_xyz" - does that require something different?

KEEPFILTERS function can only be used as a top level filter argument of CALCULATE and CALCULATETABLE or with a table argument of a function performing a table scan.

Avg Intensity SC1-2 wo xyz =

AVERAGEX (

CALCULATETABLE (

KEEPFILTERS ( VALUES ( '2_Company_tbl'[Company] ) ),

FILTER ( '2_Company_tbl', '2_Company_tbl'[Company] <> "_xyz" )

),

CALCULATE ( SUM ( '3a_GHG_Intensity SC1+2 Revenue'[Value] ) )

)

Do you see the error?

[v-shex-msft]

Hi @Dave-ExpSC,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng