DAX Average with Text Values

[cr731]

I'm trying to calculate an Average in a PowerPivot table on a column that contains numbers and blanks - but the blanks are not true blanks, but rather "" values. I need to Average where there is not a "". For instance,

Values:
1
2
3
""
4

Should calculate as (1 + 2 + 3 + 4) / 4 values that are not "".

This is the default behavior of the standard AVERAGE or use in Pivot Tables, but not so in Power Pivot. How can I write a formula that will ignore those blanks?

 

[Ozeroth]

My first suggestion would be to convert the Values column to a numeric data type (with Power Query for example), so that blanks are genuine blanks. Then AVERAGE will give you the average ignoring blanks.

If the Values column has to be text, you could use IFERROR(VALUE(...)) to ignore non-numeric text:
Average Ignoring Text := AVERAGEX( Table, IFERROR( VALUE(Table[Value]), BLANK() ) )

 

[cr731]

My first suggestion would be to convert the Values column to a numeric data type (with Power Query for example), so that blanks are genuine blanks. Then AVERAGE will give you the average ignoring blanks.

Would you mind expanding on this a little? I'm new to Power Query - would I query the existing table in Power Query, and then use Power Pivot off of that? Would doing this create a whole second set of data or would it be kept in the background?

 

[Ozeroth]

Hi again,
You should create a Power Query query that refers to your original source (Excel table or otherwise), manipulate as required, then load this Power Query table to Power Pivot. The Power Query table doesn't need to appear in the spreadsheet, but would be a connection.