Forum Discussion

ABookbin's avatar
ABookbin
Frequent Visitor
5 years ago
Solved

Filtering out negative values

Hello, 

 

I have a transportation data set that has many faulty data points. In the table below, I have averaged the 'actual transit days' by city-city pair, but somewhere within the data set there must be incorrect 'negative' days which are throwing off the average calculation. 

 

I've tried coding the average calculation to filter out values less than 0, but it doesn't change the values within the columns. Adding a general filter on the page to exclude negative 'actual transit days' also doesn't have any affect on this column. 

 

Any suggestions would be greatly appreciated!

 

Thanks!

 

average
Filter
  • Fowmy's avatar
    Fowmy
    5 years ago

    ABookbin 

    Build your measure this way:

    HISTORIC AVERAGE = 

CALCULATE(

	AVERAGE( TABLE[ACTUAL TRANSIT DAYS]),
	ALLEXCEPT( TABLE, TABLE[CITY-CITY-PAIR),
	TABLE[ACTUAL TRANSIT DAYS] > 0
)

1 Reply

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    ABookbin 

    Build your measure this way:

    HISTORIC AVERAGE = 

CALCULATE(

	AVERAGE( TABLE[ACTUAL TRANSIT DAYS]),
	ALLEXCEPT( TABLE, TABLE[CITY-CITY-PAIR),
	TABLE[ACTUAL TRANSIT DAYS] > 0
)