Forum Discussion
Solved
Help Calculating Average Excluding 2 Conditions
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')*3...
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])RETURNPoints
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!
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 %],
CALCULATE (
[EPIC %],
Master AE [EPIC Points Count] is >17,
Master AE [Quarter] is <= 2
)
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 ideaFrancesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
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.