Average excluding 0

[Analyst_PowerBI]

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 are 0, and those should not be calculated. Therefore I tried this:

CALCULATE(AVERAGEX(table;[Share]);ALLEXCEPT(Datetable;Datetable[Last 4 weeks]);FILTER(VALUES(table);[share]=0)).

With this formula I´m getting blanks whenever the share was 0.

What I need:

The average of a week, calculated without 0 or blanks, and I want to see the average also in the 0/blank colums.

I´d appreciate any help.

[v-sihou-msft · Accepted solution]

@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]

@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. 

[Anonymous]

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  )

[Ashish_Mathur]

Hi @kbrewer,

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