topic Re: Help Calculating Average Excluding 2 Conditions in DAX Commands and Tips

Help Calculating Average Excluding 2 Conditions

jabueg — Tue, 01 Jul 2025 05:45:24 GMT

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!

Re: Help Calculating Average Excluding 2 Conditions

bhanu_gautam — Tue, 01 Jul 2025 06:04:43 GMT

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
)
)

Re: Help Calculating Average Excluding 2 Conditions

jabueg — Tue, 01 Jul 2025 06:23:23 GMT

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

Re: Help Calculating Average Excluding 2 Conditions

jabueg — Tue, 01 Jul 2025 18:43:14 GMT

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,

[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!

Re: Help Calculating Average Excluding 2 Conditions

FBergamaschi — Tue, 01 Jul 2025 16:14:10 GMT

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

Re: Help Calculating Average Excluding 2 Conditions

jabueg — Tue, 01 Jul 2025 18:44:02 GMT

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.