Forum Discussion
Calcuating average for a numerical + text mixed column
HI Anonymous
Download this sample PBIX file with data and measure
You can create this measure
Sales_Avg = AVERAGEX(FILTER('Sales', IFERROR(VALUE('Sales'[Sales]) >= 0,0)), VALUE('Sales'[Sales]))Regards
Phil
Hi Phil,
This works but im confused what your iferror and value dax is doing.
Can you explain
Super UserHi Anonymous
The IFERROR( VALUE() ) is a parameter for FILTER.
What is happening is that for each value in the 'Sales'[Sales] column, try to convert it to a number using VALUE().
If that causes an error then return a 0, otherwise return the actual number.
If an error is generated the filter looks like FILTER('Sales', 0) which returns nothing, and as an error will only be generated when you try to convert a non-numeric text value to a number - this filters out any letters/words.
The final result of FILTER is a column (it's actually a table, but anyway) that consists of the values
1
2
3
4
5
6
7
8
AVERAGEX goes through each row in that column adding up the values resulting from converting the text numbers to actual numbers using VALUE('Sales'[Sales]) and then working out the average.
Regards
Phil