How do i ignore zeros when calculating an average in new measure

[Jay2022]

So I have a formula like so 

SUM DAYS = SUM(Data1[Number of weekdays])+SUM(Data2[Number of weekdays]) How do i exclude 0's in this caclulation 

To make the following give a more accurate result 

AVG = [SUM WEEKDAYS}/[COUNT OF RECORDS}

[Jay2022]

Thank you I think the problem is i'm actually counting the records based on an id but some of the weekdays that are being counted are 0 due to an end date column (of the spreadsheet) not yet being input. It's a tricky one.

[Anonymous · Accepted solution]

Hi @Jay2022 ,

I create two tables as you mentioned.

Then I create two measures and here are the DAX codes.

NonullDate1 = 
IF (
    MAX ( 'Table1'[End Date] ) = BLANK (),
    0,
    MAX ( 'Table1'[Number of weekdays] )
)

NonullDate2 =
IF (
    MAX ( 'Table2'[End Date] ) = BLANK (),
    0,
    MAX ( 'Table2'[Number of weekdays] )
)

Next you can create a new measure.

Measure = SUMX('Table1','Table1'[NonullDate1]) + SUMX('Table2','Table2'[NonullDate2])

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Jay2022 Well, if you are summing, then you are ignoring 0's. The problem then I assume is with your count of records measure which should be something like:

COUNT OF RECORDS = 
  COUNTROWS( FILTER( 'Data1', [Number of weekdays] <> 0 ) ) + COUNTROWS( FILTER( 'Data2', [Number of weekdays] <> 0 ) )