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')*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!
"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!

5 Replies

  • 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

    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!