Forum Discussion

Analyst_PowerBI's avatar
Analyst_PowerBI
Frequent Visitor
8 years ago
Solved

Average excluding 0

Hello,   I´m trying to get the average number of a week, using this DAX-Formula: CALCULATE(AVERAGEX(table;[Share]);ALLEXCEPT(Datetable;Datetable[Last 4 weeks]))   The problem is that some values...
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    Analyst_PowerBI

     

    In DAX, AVERAGE()/AVERAGEX() function will ingore entries with empty values. In your scenario, to ingore 0 values as well, you just need to put ALLEXCEPT() into FILTER() function. See my sample below:

     

    Avg Per Week =
CALCULATE (
    AVERAGE ( Table1[Sales] ),
    FILTER ( ALLEXCEPT ( Table1, Table1[Week] ), Table1[Sales] <> 0 )
)

     

    Regards,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
8 years ago

Analyst_PowerBI

 

In DAX, AVERAGE()/AVERAGEX() function will ingore entries with empty values. In your scenario, to ingore 0 values as well, you just need to put ALLEXCEPT() into FILTER() function. See my sample below:

 

Avg Per Week =
CALCULATE (
    AVERAGE ( Table1[Sales] ),
    FILTER ( ALLEXCEPT ( Table1, Table1[Week] ), Table1[Sales] <> 0 )
)

 

Regards,

  • kbrewer's avatar
    kbrewer
    Frequent Visitor
    8 years ago

    v-sihou-msft

    Ashish_Mathur

    im trying to make a similar measure to this but instead of numbers as in the "sales" column in this thread im counting instances of a specific phase. ex: if an employee completed 5 "units" in a week, the raw data will list each of those units in its own row with the date and employee name. im using a date table connected to the raw data table to show those 5 units were completed in "week 2" using the date in the raw data. ive attachd pictues of the table i have showing each employee and how many unis they completed per week. this table also shows weeks they did not complete anything. i do not want these "zero" weeks to count in the average. ive also attached a picture of part of the raw data and a picture of what columns im using from the raw data to creat the table. 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi kbrewer,

       

      Your question is not clear.  Please show a dataset and the expected result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      You have to FILTER for ISBLANK;  

      1. Filter for your selected columns;

      2. inside the selectcolumns use ALL that is SELECTED,

      3. Calculate the AVGX, averagebyrow, as this is a rollup.

      4.  AND it cannot be ISBLANK.  

       

      FILTER (  SELECTCOLUMNS...(  ALLSELECTED... ( CALCULATE... ( AVGX... (    

      AND ( NOT ( ISBLANK... ( Known[X] ) ), NOT ( ISBLANK ...( Known[Y] ) ) )

       

      Or you can state NOT EQUAL to ZERO; but in this case the AVGX Function will still COUNT, SUM, and DIVIDE the total rows.   So ignoring the Integer ZERO will not work.

      but heres the formula anyway: 

        CALCULATE(
      COUNTROWS( someTABLE),
      ALLEXCEPT( someTABLE, someTABLE[SomeColumn] ), someTABLE[someCOLUMN] <> 0  )