Forum Discussion

jabueg's avatar
jabueg
Helper I
1 year ago
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...
"average"
"DAX " "DAX help" 'Need help'
"FILTER"
Help
  • jabueg's avatar
    jabueg
    1 year ago

    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's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
jabueg
Helper I
1 year ago

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's avatar
    FBergamaschi
    Super User
    1 year ago

    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's avatar
      jabueg
      Helper I
      1 year ago

      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.