Help Calculating Average Excluding 2 Conditions

[jabueg]

I have an existing DAX measure which gets average of completed fields in the "Master AE" table (there's 35 required fields): 

[EPIC %] = CALCULATE(

  DIVIDE([EPIC Points], COUNTROWS('Master AE')*35))
The current measure gives this output on my KPI cards, which is correct:
 

However, I need to modify it to get a new average that EXCLUDES any rows where Master AE [EPIC Points Count] is <=17 AND the Master AE [Quarter] is > 2.

Or basically, exclude any results from EPIC % measure result <= 49% AND Quarter > 2.

The desired result average should be 40.28% (only 10 values would be averaged per the conditions)

Any idea how I can do this?

Here is the [EPIC points] measure which sums all the points assigned when blanks completed:

Appreciate any help!

[jabueg · Accepted solution]

I figured out the solution. Had to create a calculated a column "Filtered Points" in Table view to filter out the condition and set them to 0  - 

Filtered Points =

IF (

    'Master AE'[EPIC Points Count] <= 17 && 'Master AE'[Quarter #] > 2,

    0,

    [EPIC Points Count]

)
And then created a new measure with the new calculated column:

EPIC Points Risk =

var Points = SUM('Master AE'[Filtered Points])

RETURN

Points

Lastly, created new measure to get average of filtered points (excluding the conditions):

NEW EPIC % =

CALCULATE(

    DIVIDE([EPIC Points Risk], COUNTROWS('Master AE')*35), FILTER('Master AE', 'Master AE'[Filtered Points]<>0))

Thanks to all who helped anyway!

[bhanu_gautam]

@jabueg ,Try using

[EPIC %] =
CALCULATE(
DIVIDE(
[EPIC Points],
COUNTROWS('Master AE') * 35
),
FILTER(
'Master AE',
'Master AE'[EPIC Points Count] > 17 && 'Master AE'[Quarter] <= 2
)
)

[jabueg]

@bhanu_gautam Thanks, but it's giving incorrect result. The expected result should be 40.29%. What should I adjust in the calculation?

(85.71% + 48.57 + 34.29 + 34.29 + 31.43 + 31.43 + 31.43 +31.43 +28.57 +22.86) =  402.87/10 rows 

[FBergamaschi]

Hi,

as per my understanding, your existing measure does not need CALCULATE and should read

EPIC % =

DIVIDE([EPIC Points], COUNTROWS('Master AE')*35)

The new measure that must exclude any rows where Master AE [EPIC Points Count] is <=17 AND the Master AE [Quarter] is > 2 should yes use CALCULATE and should read

EPIC % Excl = 
CALCULATE ( 
  [EPIC %],

  Master AE [EPIC Points Count] is >17,
  Master AE [Quarter] is <= 2
)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[jabueg]

Thank you for your response, but it didn't give me the desired result. I was actually able to figure out the solution and posted in my comment.