Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"?
Your help is greatly appreciated
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/
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.
Do you see the error?
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
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |